Page 1 of 1
Order and Limit
Posted: Sat Feb 24, 2007 8:26 am
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.
Posted: Sat Feb 24, 2007 10:12 am
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.
Posted: Sat Feb 24, 2007 10:25 am
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.
Posted: Sun Feb 25, 2007 9:06 pm
by Begby
feyd | Please use 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
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]
Posted: Mon Feb 26, 2007 3:24 am
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.
Posted: Mon Feb 26, 2007 8:17 am
by Begby
feyd | Please use 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
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]