• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4041
  • Last Modified:

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
0
rmirabelle
Asked:
rmirabelle
1 Solution
 
HuyBDCommented:
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)

Open in new window

0
 
macentrapCommented:
If you have autoincrementing ID field Try this:

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

0
 
imitchieCommented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
imitchieCommented:
macentrap - wouldn't work for deleted rows....
0
 
macentrapCommented:
if its auto-incrementing than it wont matter about deleted rows of ID field
0
 
imitchieCommented:
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
 
macentrapCommented:
I know - thats why my suggestion was made based on autoincrementing ID instead of counted records
0
 
rmirabelleAuthor 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?

Thanks in advance
0
 
imitchieCommented:
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now