CODEWITHSUNDEEP
sqlmysql
Shoan
Shoan

Reputation: 4078

How do I retrieve every Nth record from a table?

The query remains constant, but the offset varies. 

SELECT NAME from tbl WHERE alphabet_index='A' limit 880,1;
SELECT NAME from tbl WHERE alphabet_index='A' limit 1760,1;
SELECT NAME from tbl WHERE alphabet_index='A' limit 2640,1;
....
SELECT NAME from tbl WHERE alphabet_index='A' limit 34320,1;

Is there a better way to do this without having to run N queries with different offsets?

Update: I am trying to build name ranges as an index for a drill down. So if an alphabet_index has 50000 records and I would like to display 40 ranges, I am calculating offset = total/rangeCount. My desired end result would be

AA...-AA...
AA...-AB...
AB...-AC...
AC...-AC...
AD...-AF...
...
...
AW...-AZ...

Upvotes: 1

Views: 1921

Answers (3)

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340191

You can do a varying offset query in a single query like this

select NAME from 
   (select @row:=@row+1 as row, t.NAME from 
    tbl t, (select @row := 0) y 
    where alphabet_index='A' order by alphabet_index) z 
where row % 880 = 1;

This will add a unique integer id to each row via the @row variable. Then it will select a row every other 880 via the modulo operator and that variable. An order by clause is required to get repeatable behavior, else the result would be effectively random.

Upvotes: 4

Lars D
Lars D

Reputation: 8563

In order to build ranges for a drilldown, I would actually fetch that one field, and fetch all records. Often, the latency of making multiple queries is larger than the time it takes to fetch that extra data. It would also solve the other problem, that multiple queries may be based on different versions of the data (unless you are using transactions).

In other words, use the very low tech solution. If you want to optimize further, use stored procedures, to make that same query, but so that the stored procedure is returning every Nth record instead of having your code filter them out:

http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html

Upvotes: 1

sventevit
sventevit

Reputation: 4816

Like this: 

with x as(
select row_number() over (order by SomeColumn) Id, *
from MyTable
)
select *
from MyTable
where Id in (1, 3, 5)

Update: my bad, this works only in MSSQL

Upvotes: 0

Related Questions