Hi Brent, I cannot think of a single query doing your job. But it can be done with two.
SELECT @maxrating := MAX(RATING) FROM NEWS WHERE RATING <= 4; SELECT * FROM NEWS WHERE RATING=@maxrating ORDER BY RAND() LIMIT 1; Anvar. At 02:36 PM 15/02/2002 -0700, you 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
