How to Select Every Nth Row in MySQL?

Selecting Every n-th Row from MySQL

In database management, it often becomes necessary to resample data to obtain a coarser representation of the original dataset. Resampling can involve selecting every n-th row, which can be useful when high-resolution data is not required.

In MySQL, there is a convenient way to perform this operation using a combination of subqueries and window functions. Here's how to select every 5th row from a MySQL table:

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

In this query, the inner subquery assigns a sequential row number to each row in the original table using a user-defined variable @row. The outer subquery then filters out the rows where the row number is not divisible by 5, resulting in a table with only the 5th, 10th, 15th, and so on rows.

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