How about
SELECT * FROM news WHERE rating <= 4 ORDER BY rating DESC, RAND() LIMIT 1; Michael On Fri, 15 Feb 2002, Brent Macnaughton wrote: > > I need some help here. Let's see if I can clearly describe it. > > I have a database containing "news" stories. Each story is assigned a > "rating" (1 thru 5) indicating its importance. (A story with a rating of 5 > is more important than a story with rating of 1.) > > Now, lets say that there are ten stories in the DB with a rating of 4 and I > want to randomly pick one of those stories. I am doing something like this: > > SELECT * FROM NEWS WHERE RATING=4 ORDER BY RAND() LIMIT 1 > > That gives me the results I want, but there is a problem. There is no > guarantee that there will be any stories in the DB with a rating of 4. If > that happens, I want to randomly select a story with the next rating (three > in this case) and if there are no stories with a rating of 3 as well, I want > to select a story with a rating of 2, and so on and so forth. > > I would really like to be able to do this with one SELECT statement as > opposed to running the above query over and over again, each time > decrementing the value of the rating until a record is returned. > > Any Suggestions? > > Thanks. > > Brent Macnaughton > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
