[RESOLVED] Help with sort by

[sidsel]

The query below is quite self explaining, selecting the last 30 people online and order by last_online.

SELECT name, level FROM table ORDER BY last_online DESC LIMIT 30;

The colomn 'level' can consist of three values 0, 1 and 2. How can I modify the query so it selects 10 rows from each 'level' = 30 rows, still ordered by last_online ?

[Piranha]

Basically there are 2 ways. Either do 3 queries and select the 10 latest in each, no need to show that. Or do UNION:

SELECT column
FROM table
WHERE level = 0
ORDER BY last_online DESC
LIMIT 10

UNION

SELECT column
FROM table
WHERE level = 1
ORDER BY last_online DESC
LIMIT 10

I think that you can do it with joins or subqueries as well, but the result you will get will be kind of messy and hard to use. Basically you will recieve 3 names and 3 levels, one from each of the levels.

[sidsel]

The union gives an error ... "Incorrect usage of UNION and ORDER BY" (mysql 4.1)

Besides wouldn't the result be ordered by last_online in 3 groups of 10?
I want the 30 rows to be ordered by last_online independent of 'level'.

[Piranha]

You may have to use parantheses. And maybe put all of this as a subquery to be able to order everything by the date. Or maybe there are another way to do this that I don't know about that is much better.

I would probably think about doing all this with 3 queries if noone else comes with a good solution, and change it if it is needed for performance or other in the future.

[sidsel]

(SELECT column
FROM table
WHERE level = 0
ORDER BY last_online DESC
LIMIT 10)

UNION

(SELECT column
FROM table
WHERE level = 1
ORDER BY last_online DESC
LIMIT 10)

UNION

(SELECT column
FROM table
WHERE level = 2
ORDER BY last_online DESC
LIMIT 10)

ORDER BY last_online DESC

The above seems to do the trick but it's not pretty 🙂

[Sxooter]

Two points. One, you don't need all the order bys, the last one is all you need, and you can do union ALL since they will all be distinct rows. regular unionx do an extra sort / unique step that you don't need here. Also, the use of limit in the queries might need it to stay a set of subselects. If so, then just add the parens back in that I took out.

SELECT name, level, last_online
FROM table 
WHERE level = 0 
LIMIT 10
UNION ALL
SELECT name, level, last_online
FROM table 
WHERE level = 1 
LIMIT 10
UNION ALL
SELECT name, level, last_online
FROM table 
WHERE level = 2 
LIMIT 10
ORDER BY last_online DESC 

If you need subselects, this should work:

select * from 
(SELECT name, level, last_online
FROM table 
WHERE level = 0 
LIMIT 10
UNION ALL
SELECT name, level, last_online
FROM table 
WHERE level = 1 
LIMIT 10
UNION ALL
SELECT name, level, last_online
FROM table 
WHERE level = 2 
LIMIT 10) as a
ORDER BY a.last_online DESC 

[sidsel]

Sxooter,

I tried out your example and all the order bys do need to be there otherwise the 3 * selects select just the first 10 rows in the table, not the last 10 online.

[Sxooter]

duh, of course, what was I thinking?