MySQL – select every Nth record

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:

select-nth_rows1

10 Replies to “MySQL – select every Nth record”

  1. 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.

  2. For the life of me, I cannot get this to work for me. Anyone know if and at what version this becomes incompatible?

  3. 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?

  4. @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?

  5. Curious. What tool is that in the screenshot where you performed the query with the resulting rows?

  6. 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:[email protected]+1 as rownum, mytable.* FROM (
          SELECT @row:=0
       )r,mytable
    ) 
    ranked 
    WHERE rownum IN(__HERE__IS_THE_LIST_OF_THE_RANDOM_NUMBERS)

Comments are closed.