Retrieving Random Sections From A Data file

On my company's website, we have a quote of the day. I would like to be able to type a hundred or so quotes into a mdf file. Then, I would like to have code that randomly selects one of the quotes every day and posts it.

...What I want is very similar to the "Image Of the Day" section on many websites.

Any ideas?

Okay, I'll bite. Bear in mind that there are CMS out there that offer this - but if you want to roll your own...several approaches come to mind, so you'll want to pick the one with the best performance.Here's one - in steps. (a) Create a table with at least two fields - your quote text and an autoincrement ID (i.e. first record is 1, second is 2 etc). (b) Get the quotes into the table. (c) In your aspx, programmatically get a random integer bounded between 1 and the number of quotes you have - and select that ID from the table for display.Alternatively, have a second table that will only have one quote in it. Have your webpage simply display that one record. Create a stored procedure in the database that removes the existing record and copies in a quote from the full quote table (based again on generating a random ID - the rand() function does the trick in SQL). Then set up a daily scheduled job that calls the stored procedure which changes the quote.|||

Thanks. This looks good. Do you have any tips on what sort of code I should write to obtain the random integer?

|||

Hi,

Try this, select top 1 * from TableName order by NEWID();

Thanks.

Retrieve first row only in many-to-many relationship

I have a db with three tables - books, sections, and a joining table.
The normal way of getting a many to many relationship (i.e. one book
may belong to many sections, and one section may contain many books)

I want to extract the data with a single row for each book so that I
only retrieve the first section description for any book. (e.g. title,
author, section, description)

Structure as follows:

tbl_book
book_id, title, author, description etc...

tbl_section
section_id, section_desc

tbl_book_section
book_id, section_id

DBA is away and I can't figure this out at all...any help gratefully
received.Try this. I'm assuming that by "first section" you mean the lowest numbered
section_id.

SELECT B1.book_id, B1.title, B1.author, B1.description,
S2.section_id, S2.section_desc
FROM tbl_book AS B1
JOIN
(SELECT book_id, MIN(section_id) AS section_id
FROM tbl_book_section
GROUP BY book_id) AS S1
ON B1.book_id = S1.book_id
JOIN tbl_section AS S2
ON S1.section_id = S2.section_id

--
David Portas
----
Please reply only to the newsgroup
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Since SQL is a set-oriented language, there is no such concept as a
first row in a table. The next basic principle is that all
relationships are shown as values in a column. Therefore, you must
have a section number of some kind in the DDL that you did not post
for this to make sense.

Book_id ought to be an ISBN, but we have no idea what section_id is
like and if it has an ordering.

When the DBA gets back, ask him to read and use ISO-11179 naming
standards. What he ias given you says that you only have one book
about furniture, specifically tables.|||David

That did the trick thanks.

Gareth

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<ceydnc72CbDgi5XdRVn-gQ@.giganews.com>...
> Try this. I'm assuming that by "first section" you mean the lowest numbered
> section_id.
> SELECT B1.book_id, B1.title, B1.author, B1.description,
> S2.section_id, S2.section_desc
> FROM tbl_book AS B1
> JOIN
> (SELECT book_id, MIN(section_id) AS section_id
> FROM tbl_book_section
> GROUP BY book_id) AS S1
> ON B1.book_id = S1.book_id
> JOIN tbl_section AS S2
> ON S1.section_id = S2.section_id|||Joe

I don't know what you mean by DDL, but the other guy who posted a
reply clearly understood what I was asking about.

The database in question existed before the DBA (female by the way)
joined the company and the reason I want the query is to extract the
data for a new ecommerce system.

Naming conventions are indeed a good thing...

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0401161012.58f5d22a@.posting.google.com>...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications.
> Since SQL is a set-oriented language, there is no such concept as a
> first row in a table. The next basic principle is that all
> relationships are shown as values in a column. Therefore, you must
> have a section number of some kind in the DDL that you did not post
> for this to make sense.
> Book_id ought to be an ISBN, but we have no idea what section_id is
> like and if it has an ordering.
> When the DBA gets back, ask him to read and use ISO-11179 naming
> standards. What he ias given you says that you only have one book
> about furniture, specifically tables.|||> I don't know what you mean by DDL, but the other guy who posted a
> reply clearly understood what I was asking about.

I guessed what you wanted but it is useful to post DDL for questions like
this:
www.aspfaq.com/5006

--
David Portas
----
Please reply only to the newsgroup
--|||"Gareth" <gareth900@.hotmail.com> wrote in message
news:c105346f.0401170201.51b5e4b1@.posting.google.c om...
> Joe
> I don't know what you mean by DDL, but the other guy who posted a
> reply clearly understood what I was asking about.

DDL - Data Description Language.

Basically the SQL commands to create the tables with keys, constraints, etc.
that you want. This allows folks answering your question to create a test
setup on their own servers. Generally you'll get answers that have been
fully tested that way.

Joe Celko is a bit of curmudgeon, but he's also arguably one of the better
experts on the SQL language out there. He has several books to his name and
knows his stuff. And yes, he's opinionated. :-)

> The database in question existed before the DBA (female by the way)
> joined the company and the reason I want the query is to extract the
> data for a new ecommerce system.
> Naming conventions are indeed a good thing...|||Greg - DDL - makes sense now...

In future I'll do this - didn't realise the conventions in the group.

Thanks

Gareth|||>> I don't know what you mean by DDL .. <<

Data Definition Language. SQL has three sublanguages and this is one of
them. It is also the minimal netiquette in SQL newsgroups.

>> the other guy who posted a reply clearly understood what I was asking
about. <<

No, he guessed lucky. What if section_id had been a title, like
"Introduction" or "preamble" which was not in alphabetic order?

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> Joe Celko is a bit of curmudgeon, ... <<

Hey, if I had any friends, they'd tell you what a great guy I am!|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0401191734.1252bc8e@.posting.google.c om...
> >> Joe Celko is a bit of curmudgeon, ... <<
> Hey, if I had any friends, they'd tell you what a great guy I am!

Hey, you say that like I was saying something that wasn't nice. :-)