Order and Limit

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Order and Limit

Post by onion2k »

I have a table of data which is basically "title" and "score". I need to get the top 25 scoring records, and order them by the title. I can do it with a subquery, which is ok for the time being, but it's something that will appear on every page so I'd prefer to optimise it to a single query if possible. Is that possible?

UPDATE: The subquery idea doesn't work. How annoying.

In essence I have a table:

Code: Select all

id     label     score
1      l1        17
2      l2        20
3      l3        5
4      l4        9
5      l5        34
6      l6        14
7      l7        20
8      l8        3
9      l9        91
10     l10       40
I need the top 5 by score, ordered by label. It looks easy. I'm stumped.
Last edited by onion2k on Sat Feb 24, 2007 10:14 am, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Quickly running through queries in my head, I can't come up with one that would work without being extremely complicated outside of a subquery.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Solved it by ordering by the score and then stuffing everything into a PHP array and sorting that by the label. Not ideal. If anyone is clever enough to work out how to order and limit by different columns I'd be very interested.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[syntax="sql"]
SELECT t.*
FROM
(
 SELECT label, score
 FROM table
 ORDER BY score
 LIMIT 25
) as t
ORDER BY t.label
This subquery should work (possibly, I pulled it out of my butt and I am tired). Unfortunately I cannot think of an easy way to do this using joins, but the query above should be quick assuming that score has an index.

Also, the solution you came up with is not a bad one. Ordering a 25 record array in php on every page load is not very costly.


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Begby wrote:

Code: Select all

SELECT t.*
FROM
(
 SELECT label, score
 FROM table
 ORDER BY score
 LIMIT 25
) as t
ORDER BY t.label
This subquery should work (possibly, I pulled it out of my butt and I am tired). Unfortunately I cannot think of an easy way to do this using joins, but the query above should be quick assuming that score has an index.

Also, the solution you came up with is not a bad one. Ordering a 25 record array in php on every page load is not very costly.
That was my original subquery idea, but MySQL 4.1 doesn't support limits in subquerys so it doesn't work.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Try this one.  This will select the top 25 score ordered by label (renamed to slabel since label is reservered).

Note, this will select the top 25, so if there are 3 people that all got 99, then there will be 27 rows in your result.  Eliminating the DISTINCT keyword will make it so there are only 25 results, but the bottom people in the rankings will get dropped depending on the order they appear in the table I believe.

[syntax="sql"]
SELECT slabel, score, rnk
FROM
(
 SELECT, t1.score, t1.slabel,
 (
  SELECT COUNT(DISTINCT t2.score)
  FROM scores t2
  WHERE t1.score < t2.score
 ) AS rnk
 FROM scores t1
 ORDER BY rnk
) AS scores
WHERE rnk < 25
ORDER BY slabel

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Post Reply