SQL Pick by Index When Using ORDER BY

Question

I want to pick say the 10th, 20th, and 50th entry in a dataset after it has been ordered by a column. What's the best way to achieve this?

What DB engine do you use? – juergen d Nov 15 '14 at 09:52 — juergen d, Nov 15 '14 at 09:52
Go with SQLite for the moment. – Jacob Nov 15 '14 at 09:56 — Jacob, Nov 15 '14 at 09:56

Michał Komorowski · Answer 1 · 2014-11-20T08:13:50.137

0

Let's assume that we have the following table:

create table Test
(
  value int
);

Here is a query that returns first, third and sixth row:

select value
from 
(
  select value, (select count(*) + 1 from Test t2 where t2.value < t1.value) as OrderId
  from Test t1
) tbl
where tbl.OrderId in (1,3,6)

You can try it here. If there are duplicates in the Test table the solution above can return more than 3 rows.

UPDATE

If you want to sort by different column than value from my example you should modify the condition t2.value < t1.value. The general form is t2.COLUMN_NAME < t1.COLUMN_NAME.

edited Nov 20 '14 at 08:13

answered Nov 15 '14 at 10:10

Michał Komorowski

6,198
1
20
24

I changed your table to contain `id, productname` in this [modified sqlfiddle](http://sqlfiddle.com/#!7/46fa8/2). If i am not mistaken than the record `12, 'Bar 12'` is omitted which i would not expect. – surfmuggle Nov 19 '14 at 21:06
Do you want a dataset to be ordered by the id or the product name column? – Michał Komorowski Nov 19 '14 at 21:34
Are you asking me or OP? Based on my understanding of `after it has been ordered by a column` the question of OP includes the option to be ordered by any column. – surfmuggle Nov 19 '14 at 21:38
If we sort by id column then the record `12, 'Bar 12'` will have OrderId = 7. If we sort by the product name column then the record `12, 'Bar 12'` will have OrderId = 3 (the same as the record `14, 'Bar 12'`). None of these cases fullfills `tbl.OrderId % 2 ==0` condition. Besides in your example you added `Order by productname` to the query but you didn't change the condition `t2.id < t1.id` to `t2.productname< t1.productname`. – Michał Komorowski Nov 20 '14 at 08:10

score 0 · Answer 2 · answered Nov 15 '14 at 12:19

The easiest and most efficient way is to just use LIMIT/OFFSET:

SELECT * FROM MyTable ORDER BY whatever LIMIT 1 OFFSET 9
UNION ALL
SELECT * FROM MyTable ORDER BY whatever LIMIT 1 OFFSET 19
UNION ALL
SELECT * FROM MyTable ORDER BY whatever LIMIT 1 OFFSET 49

SQL Pick by Index When Using ORDER BY

2 Answers2