# how to select every nth row

how might I select every nth row of a larger query?
For instance, I have a query that returns 1000 records, but I want to display only the 1st, 5th, 10, etc
LVL 1
###### Who is Participating?

Commented:
here's an example using some joomla tables:
the inner query sets up the row numbers, the outer query in this case causes rows 1,5,9,13.. to be selected

SELECT *
FROM (
SELECT @row := @row +1 AS rownum, sum( catid ) AS somefield, date( created )
FROM (SELECT @row :=0) r, lf_content t1
LEFT OUTER JOIN lf_categories t2 ON ( t1.catid = t2.id )
WHERE t1.id IS NOT NULL
GROUP BY date( created )
HAVING somefield >0
)ranked
WHERE rownum %4 =1
0

Commented:
If your table have a PK, try this

HuyBD
``````select * from (select (select count(*) from yourtable where id<t.id) as no,t.* from yourtable as t) as M where no in(1,5,10)
``````
0

Commented:
If you have autoincrementing ID field Try this:

SELECT * FROM Items WHERE ID = '1' or ID % 5 = 0
Order by ID Asc

0

Commented:
while huyBD is correct, doing that count so many times is a bit inefficient

SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t

WHERE rownum %5=0
and <other conditions>
ORDER BY ....

where T is your original table
%5=0 means every row that is a multiple of 5
%6=0 means every row that is a multiple of 6
etc
0

Commented:
macentrap - wouldn't work for deleted rows....
0

Commented:
if its auto-incrementing than it wont matter about deleted rows of ID field
0

Commented:
i'm going to assume that
every nth row... 1st, 5th, 10, is a mistype, that it should actually be
5th, 10th, 15th etc.
i.e. that the questioner is after a sampling of data skipping rows, not by id but really by number of rows in the "would-be result without skipping"

SELECT * FROM Items WHERE ID = '1' or ID % 5 = 0
Order by ID Asc

if my remaining records are IDs 2,4,6,8,11,23,26,28,29,31,35
your return result based on ID will be... nothing?
0

Commented:
I know - thats why my suggestion was made based on autoincrementing ID instead of counted records
0

Author Commented:
I think I'm following the suggestions, but am getting confused about how to include the subquery in the context of a more complex sql statement:

SELECT somefield, thedate FROM t1 LEFT OUTER JOIN t2 ON (t1.fk = t2.pk) WHERE t1.anotherfield NOT NULL GROUP BY thedate HAVING somefield > 0

any way to mix the statement in here with the left outer join and the having clause?