MySQL – select every Nth record
February 20th, 2009
Inspired by posts by Ray Camden and then Adrian J. Moreno, I’ve come up with the same solution for MySQL:
SELECT * FROM ( SELECT @row := @row +1 AS rownum, noun FROM ( SELECT @row :=0) r, nouns ) ranked WHERE rownum %4 =1
The table name is ‘nouns’. The column with data we want is ‘noun’.
As you can see from the return, it is working. This table has over 752 rows, but MySQL gave us every 4th:
Nifty, but I can’t really think of a practical use for this.
It would be good for a disperse sampling of data from a very large table. You don’t want a chunk of 100 rows from just the front, middle, or end.
For the life of me, I cannot get this to work for me. Anyone know if and at what version this becomes incompatible?
@Dean
I don’t know the answer to that. But I can tell you that the database server I am using is MySQL 5.1.30-community.
Intriguing. You made a superb point.
so say for example – I want to display ALL the rows in the resultset, BUT, every nth row I want to display a SPECIFIC row?
How would the coding work with that?
@Craig
I’m not really sure what you mean. If you want to show every row you would just do a normal select without limits. But then specific rows, too?
Curious. What tool is that in the screenshot where you performed the query with the resulting rows?
@Sam M.
That would be Querious: http://www.araelium.com/querious/
Though I am now a Sequel Pro user: http://www.sequelpro.com/ It is ssuuppeerr fast.
Hi!
This is a nice one!
I often need every Nth row. For example if you want to visualize the data of your experiments and in Excel you can show only 32000 datapoints.
With small modifications this statement can be used for efficient random selecting of the table records.
You generate as many random numbers in the range [1 , count(*)] as the number of records you need and execute the following statement:
SELECT * FROM ( SELECT @row:=@row+1 as rownum, mytable.* FROM ( SELECT @row:=0 )r,mytable ) ranked WHERE rownum IN(__HERE__IS_THE_LIST_OF_THE_RANDOM_NUMBERS)