How to Select Every Nth Row from a MySQL Database?

Selecting Every N-th Row from MySQL Databases

To extract specific intervals of data from a MySQL database, consider resampling the data by selecting every nth row. This technique is useful when reducing the resolution of a large dataset while retaining essential trends.

Solution 1: Using a Rownum Counter and Modulo Calculation

This approach utilizes a rownum column to assign row numbers to each data point and then performs a modulo operation to filter the rows. The following query selects every 5th row:

SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, [column name] 
    FROM ( 
        SELECT @row :=0) r, [table name] 
    ) ranked 
WHERE rownum % 5 = 1 

The above is the detailed content of How to Select Every Nth Row from a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!