How can I select 10th, 20th, 30th ... row of the result of another select query

Question

I have a table with a column to, from and ID. I need to select only rows with every 10th ID which is from A to B.

If your DBMS supports Windowed Aggregate Function you can simply use a `ROW_NUMBER` modulo 10 — dnoeth, Jan 19 '16 at 09:44
then it's ok for ROW_NUMBER you can just go like dnoeth said — Raffaello.D.Huke, Jan 19 '16 at 09:57

sagi · Accepted Answer · 2016-01-19T10:12:33.673

4

select * from 
(select * from table where from = 'A' and to ='B' order by ID)
where mod(rownum/10,1) = 0

It first takes only those from 'A' to 'B', then giving them rownums, and selecting only those in the 10th 20th ETC places..

edited Jan 19 '16 at 10:12

answered Jan 19 '16 at 09:59

sagi

1

You have to use order by: (select * from table where from = 'A' and to ='B' ORDER BY ID) – Jean-Christophe Blanchard Jan 19 '16 at 10:10
Good. You could have used `mod(rownum,10) = 0`. – Tony Andrews Jan 19 '16 at 10:20
@sagi its not working..I found something in another post http://stackoverflow.com/questions/15963129/how-to-use-rownum – lokesh kumar Jan 20 '16 at 09:56

score -1 · Answer 2 · edited Jan 21 '16 at 07:30

-1

Or you can just use

select from, to, id 
from table1 
where id like'%0' and from ='A' and to = 'B'

edited Jan 21 '16 at 07:30

Dmitriy

answered Jan 19 '16 at 09:59

Raffaello.D.Huke

Who said the ID's are continuously ? he wants the 10th not id = 10 and id = 20 – sagi Jan 19 '16 at 10:01

2 Answers2