How to Select Every Nth Row from a MySQL Database for Optimized Data Visualization?

Resampling Database Data by Selecting Every n-th Row

Question:

To create a line chart, you have a series of values in a database that you need to extract. However, to optimize for performance and clarity, you want to retrieve only every 5th row. How can you achieve this using MySQL?

Answer:

To select every n-th row from a MySQL table, follow these steps:

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

Breakdown:

• Create a user-defined variable @row and initialize it to 0 within a subquery.
• In the main subquery, select the [column name] and increment @row by 1 to assign row numbers.
• Use an outer query to select from the ranked subquery.
• Apply the modulo operator (%) to identify rows where rownum divided by [n] (e.g., 5) leaves a remainder of 1. This selects every n-th row.

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