CODEWITHSUNDEEP
sqlsybase
avinashse
avinashse

Reputation: 1460

find every 5th record from table

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.

Upvotes: 1

Views: 2784

Answers (4)

Robert
Robert

Reputation: 25753

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

Upvotes: 0

valex
valex

Reputation: 24144

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

Upvotes: 1

SRIRAM
SRIRAM

Reputation: 1888

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.

Upvotes: 0

duffymo
duffymo

Reputation: 308793

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.

Upvotes: 0

Related Questions