Re: Selecting records with the highest value no greater than x
Hi Benjamin, The perfect answer. Thank you, Anvar. At 08:44 AM 16/02/2002 +0100, you wrote: >Hi. > >On Fri, Feb 15, 2002 at 09:05:02PM -0800, [EMAIL PROTECTED] wrote: > > 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. > >IMO, there is one, if I did understand the question correctly: > >SELECT * FROM NEWS WHERE RATING <= 4 ORDER BY RATING DESC, RAND() LIMIT 1; > >This give back a random news entry of the highest score available, but >smaller than 5. > > > >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; >[...] > > Nice, assuming that the " @maxrating " is the syntax for a local > > variable within the server. Where is that discussed in the manual? > >They can be found under the term "user variables": >http://www.mysql.com/doc/V/a/Variables.html > > > Some simple use of an API is the probably the best answer, it's > > probably ~ 25 lines of perl. >[...] > >Depends on the needs. A pure SQL solution should be noticeably faster. > >Bye, > > Benjamin. > >-- >[EMAIL PROTECTED] - 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
RE: Selecting records with the highest value no greater than x
* [EMAIL PROTECTED] > In Re: Selecting records with the highest value no greater than > x, <[EMAIL PROTECTED]> wrote: > > > >IMO, there is one, if I did understand the question correctly: > > > >SELECT * FROM NEWS WHERE RATING <= 4 ORDER BY RATING DESC, > > RAND() LIMIT 1; > > > >This give back a random news entry of the highest score available, but > >smaller than 5. > > > It just gives back a random news story, as it is logically identical to > > SELECT * FROM NEWS WHERE RATING <= 4 ORDER BY RAND() LIMIT 1; No, it's not. The former will sort on the rating first, and then do a random sort within each rating group. > You would need a 'non-uniform' random function for your version to work. I don't understand why you say this. Benjamin's ORDER BY clause will select a random row among those having the highest rating lower or equal to four, just as one would expect from the statement. -- Roger query - 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
Re: Selecting records with the highest value no greater than x
In Re: Selecting records with the highest value no greater than x, <[EMAIL PROTECTED]> wrote: > >IMO, there is one, if I did understand the question correctly: > >SELECT * FROM NEWS WHERE RATING <= 4 ORDER BY RATING DESC, RAND() LIMIT 1; > >This give back a random news entry of the highest score available, but >smaller than 5. It just gives back a random news story, as it is logically identical to SELECT * FROM NEWS WHERE RATING <= 4 ORDER BY RAND() LIMIT 1; You would need a 'non-uniform' random function for your version to work. Thanks for the user variable URL. - 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
Re: Selecting records with the highest value no greater than x
Hi. On Fri, Feb 15, 2002 at 09:05:02PM -0800, [EMAIL PROTECTED] wrote: > 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. IMO, there is one, if I did understand the question correctly: SELECT * FROM NEWS WHERE RATING <= 4 ORDER BY RATING DESC, RAND() LIMIT 1; This give back a random news entry of the highest score available, but smaller than 5. > >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; [...] > Nice, assuming that the " @maxrating " is the syntax for a local > variable within the server. Where is that discussed in the manual? They can be found under the term "user variables": http://www.mysql.com/doc/V/a/Variables.html > Some simple use of an API is the probably the best answer, it's > probably ~ 25 lines of perl. [...] Depends on the needs. A pure SQL solution should be noticeably faster. Bye, Benjamin. -- [EMAIL PROTECTED] - 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
Re: Selecting records with the highest value no greater than x
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
Re: Selecting records with the highest value no greater than x
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
Re: Selecting records with the highest value no greater than x
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
Selecting records with the highest value no greater than x
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
