In Re: Selecting records with the highest value no greater than x, <[EMAIL PROTECTED]> wrote: > >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.
Nice, assuming that the " @maxrating " is the syntax for a local variable within the server. Where is that discussed in the manual? Some simple use of an API is the probably the best answer, it's probably ~ 25 lines of perl. For instance the reader may be using the system for a second time, or nth time, and would certainly wish to get a different story each time. Either of the examples here completely miss the stories (however rare) of rating 5, and the reader probably wants to be able to control that rating selection level too. With a little API work, you can add niceties such as the number of stories at each rating level, etc. I've been using it for months with a minimal background initially, there was little startup 'pain'. >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 --------------------------------------------------------------------- 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
