MySQL: Order, Limit, Order
I try to program a solution where I have multiple linked tables. Now I have another problem:
I want to limit count of returned lines to 1000. But I want to show ID 1-1000, next page 1001-2000. IDs may be stored in iregular order in database (ID 1 does not have to be the first row)
Not any problem so far:
SELECT * FROM table ORDER BY id ASC LIMIT 1000
BUT Now I have to sort resulty by another column:
SELECT * FROM table ORDER BY name ASC LIMIT 1000
which might return other IDs than 1-1000 or I do
SELECT * FROM table ORDER BY id ASC, ORDER BY name ASC LIMIT 1000
But this will only sort by ID an then by name. So if I would have any ID as duplicate (which is not possible) I would then have those sorted by name.
How can I achive that I get the first 1000 IDs (some IDs might not exist as they might have been deleted before!) and those thousand rows sorted by name?
Is that even possible?
SELECT * FROM table WHERE id IN ( SELECT ID FROM table ORDER BY ID ASC LIMIT 0, 1000 ) ORDER BY name ASC
As mentioned in comments that subquery is not supported, 'This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'. Using JOINs is the only alternative left, like this:
SELECT table.* FROM tab JOIN ( SELECT ID FROM table ORDER BY ID LIMIT 1000 ) temp ON table.ID = temp.ID ORDER BY table.name