2

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?

Mr. Radical
  • 1,847
  • 1
  • 19
  • 29
FSE dark alex
  • 69
  • 3
  • 11

1 Answers1

4

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
Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
  • 1
    +1, and may I ask you question? I wonder if following query performs identically yours or differently. If differs from each other, which is faster? `SELECT * FROM ( SELECT id FROM tab ORDER BY id ASC LIMIT 1000 ) x INNER JOIN tab ON (x.id = tab.id) ORDER BY tab.name LIMIT 1000` – Jason Heo Dec 09 '13 at 11:46
  • @InoSHeo it looks that your query would also return the same data. It uses JOIN instead of subquery. As for the performance, try both queries and check them using the [EXPLAIN](http://dev.mysql.com/doc/refman/5.0/en/explain.html) statement which will give you the query execution plan. Generally JOINs are faster. You may also check [Join vs. sub-query](http://stackoverflow.com/questions/2577174/join-vs-sub-query). – Aziz Shaikh Dec 09 '13 at 11:55
  • Aziz, Thanks. @FSEdarkalex If above two queries are what you want, Could you test both queries on your data? I want to know results with real data. – Jason Heo Dec 09 '13 at 12:02
  • /* SQL Fehler (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' */ so far for the subquery //EDIT: And the join is wrking – FSE dark alex Dec 09 '13 at 14:11
  • @FSEdarkalex hmmm ... no option left other than trying the JOIN approach. – Aziz Shaikh Dec 09 '13 at 14:22