0

Let's say I have a query

SELECT *
FROM foo
ORDER BY id

And I want the 1st, 5th, 100th, 250th result, etc.

SELECT *
FROM foo
ORDER BY id
LIMIT 5, 1

Gives you the 5th. And I could run a similar query N times. But, how can I do it more elegantly in only one query?

Something like this (which doesn't work) would be amazing!

 LIMIT 1, 5, 100, 250
Gidon Wise
  • 1,896
  • 1
  • 11
  • 11
  • Maybe make it about 1,3,5,7 and 11, and see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Aug 25 '19 at 15:44
  • both answers are good and i upvoted both, but i gave the check to the old school. but if u r using 5.8 u might reconsider. – Gidon Wise Aug 25 '19 at 18:51

2 Answers2

4

One way would be ROW_NUMBER(MySQL 8.0):

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS rn FROM foo) sub
WHERE rn IN (1, 5, 100, 250)
ORDER BY rn;
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

You can achieve it by using a variable for MySQL >= 5.x.x

SELECT z.*
FROM (
    SELECT *, @row_number:=@row_number+1 AS row_number
    FROM foo, (SELECT @row_number:=0) AS t
    ORDER BY pkey
) AS z
WHERE row_number IN (1, 5, 100, 250);

Here the variable is declared as a table t. @row_number will be incremented by 1 for each row which can then be filtered using WHERE clause.

Samir Selia
  • 7,007
  • 2
  • 11
  • 30
  • the inner SQL really need to have a ORDER BY as SQL is **orderless** by ANSI/ISO SQL standards.. – Raymond Nijland Aug 25 '19 at 15:29
  • *"You can achieve it by using a variable for MySQL 5.x.x"* By the way MySQL 8 also supports user variables it's from around MySQL 5.1 and up where MySQL supports it.. – Raymond Nijland Aug 25 '19 at 15:34
  • Yes, thought to put as an alternative answer to Lukasz where I missed adding >= to the version number. Thanks again! – Samir Selia Aug 25 '19 at 15:36