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?

Answers


Try this:

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

Need Your Help