find every 5th record from table

Question

There are n record in a table ABC . write a query to select every 5th record from the table.

for example there are 30 rows in a table. so query should output 5th,10th,15th,20th, 25th and 30th record from the table.

I tried ROW_NUMBER OVER (ORDER BY id ) but getting msg :-

Message from SQL server IML (msg 156, level 15, state 2):

Incorrect syntax near the keyword 'OVER'.

I am using Sybase database.

How would you determine which row is the 5th? – SWeko Dec 26 '12 at 10:28 — SWeko, Dec 26 '12 at 10:28
`ROW_NUMBER() OVER` doesn't work on SYBASE! – Robert Dec 27 '12 at 08:22 — Robert, Dec 27 '12 at 08:22
@Parado : how can I solve the problem :( – avinashse Dec 27 '12 at 09:39 — avinashse, Dec 27 '12 at 09:39
@avinashse I added the solution. – Robert Dec 27 '12 at 09:57 — Robert, Dec 27 '12 at 09:57

valex · Answer 1 · 2012-12-27T07:56:22.710

1

You need to define order to get every 5th row. It can be for example PRIMARY KEY ID or something like this

select * from
(
  select ABC.*, ROW_NUMBER() OVER (ORDER BY id) as RN from ABC
) t1
where RN % 5 = 0

edited Dec 27 '12 at 07:56

answered Dec 26 '12 at 11:32

valex

23,966
7
43
60

I've changed the query. Try it now. – valex Dec 27 '12 at 05:47
showing agani `Message from SQL server IML (msg 156, level 15, state 2): Incorrect syntax near the keyword 'OVER'. ` error – avinashse Dec 27 '12 at 06:32
Make sure [ROW_NUMBER() OVER](http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1520/html/iqrefbb/iqrefbb262.htm) goes with brackets. It is a function. – valex Dec 27 '12 at 07:01
I am querying :- `select * from (select test_sal.*, ROW_NUMBER() OVER(ORDER BY id) as RN from test_sal) where RN % 5 = 0; ` getting the same error – avinashse Dec 27 '12 at 07:36

score 0 · Answer 2 · answered Dec 26 '12 at 10:20

0

This is not how SQL works. I'm not sure which column would give you what you want, but you aren't supposed to know or care about how the data is stored or what the ordering might be.

You need to figure out a WHERE clause that gives you what you want. Something depending on order is wrong.

answered Dec 26 '12 at 10:20

duffymo

305,152
44
369
561

If sypace dan calculate a row number (which most sql can) you can use that to filter (%5) in a select on the previous query. Stnadard for example for paging etc. See answer by valex. – TomTom Dec 27 '12 at 06:03

score 0 · Answer 3 · answered Dec 26 '12 at 10:22

0

try this

  select rn, col_list from (select rownum rn, col_list from tab_name)
  where rn=N;

where N is the row number you want.

answered Dec 26 '12 at 10:22

SRIRAM

1,888
2
17
17

score 0 · Answer 4 · answered Dec 27 '12 at 09:56

0

Try this way.

Create temporary table with identity column.

select 
id = identity(8),
column1,....
into #ABC
from ABC

Select with modulo:

select * from #ABC
where  id % 5 = 0

answered Dec 27 '12 at 09:56

Robert

25,425
8
67
81

find every 5th record from table

4 Answers4