SQL Server, select a record every 'x' amount of records

Question

enter image description here

Hi,

I have “Table1” as shown above with some records on it and the column names are Id, Name and [Entry Date]. Column Id is primary key, I am using SQL Server 2005.

I want to write a query that returns information every 5 records, for example my query will return:

enter image description here

As you can see in the final result only the record every 5 records was selected, how can I accomplish this?

Thank you

Take a look at this... http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table and this http://msdn.microsoft.com/en-us/library/ms190279.aspx. The trick is to use SQL's ROW_NUMBER() OVER and the modulo command. — Jeremy Armstrong, Jan 29 '12 at 17:12
you cant do this with sql query UNLESS sql server provides a way to trigger a procedure every x amount of seconds, otherwise you will need to use some language, you will also have to keep track of the data you have fetched. — Shaheer, Jan 29 '12 at 17:14
1, 5, 10, 15. That isn't every 5. The first difference is 4, whereas all subsequent are 5. Do you mean 1, 6, 11, 16, ....? — , Jan 29 '12 at 17:19
@Shark, thanks for input. The number of records to skip is not relevant at this point. I just wanted to be able to skip certain number of records every 'n' number, so it can be 4 or 5 or 6. But thanks. — Fayde, Jan 29 '12 at 18:17

score 18 · Accepted Answer · edited Aug 17 '17 at 14:53

18

SELECT id, name, entryDate 
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) rowNumber, Id, name, entryDate
          FROM MyTable)
  WHERE rowNumber = 1
     OR rowNumber % 5 = 0

edited Aug 17 '17 at 14:53

René Hoffmann

2,766
2
20
43

answered Jan 29 '12 at 17:23

Bassam Mehanni

14,796
2
33
41

1

Heh! You even cater for what is surely a mistake in the loigc on the OPs part, nice :) – MatBailie Jan 29 '12 at 17:37
Thank you Bassam, the solution you offerd works as intended.I accept your answer. – Fayde Jan 29 '12 at 18:18

score 0 · Answer 2 · answered Jan 29 '12 at 17:18

0

select * from mytable 
where id = 1 or id % 5 = 0

answered Jan 29 '12 at 17:18

juergen d

201,996
37
293
362

This won't work if there are gaps between ids due to deletion – Bassam Mehanni Jan 29 '12 at 17:25
Assumes all records are sequential. No deletes. No failed inserts. Etc – MatBailie Jan 29 '12 at 17:36

score 0 · Answer 3 · answered Jan 29 '12 at 17:19

Assuming the IDs are sequential and unique (which they appear to be), just index += 5 for every record. Some pseudo code for this:

$index = 0;
while ( $index <= $totalRecords ) {
   $result = "select * from records where id='$index'";
   /*process results*/
   $index += 5; /*increment index for next query*/
}

Note that for record ID=1, will be a special case since your IDs start at 1, and not 0.

Hope this gives you an idea.

SQL Server, select a record every 'x' amount of records

3 Answers3