Mysql - Get row number on select

How to get row numbers before MySQL 8.0+

Back in the day, when dinosaurs roamed and ROW_NUMBER() didn't exist yet, you'd fire session variable @rank into action. Check this out:

SET @rank := 0;
SELECT @rank := @rank + 1 AS row_num, column_names
FROM your_table
ORDER BY column_name;


Make sure @rank is set to 0 before blasting off the SELECT statement, otherwise, MySQL won't know where to start counting from.

Effectively using subqueries

When it comes to grouping rows, and keeping track of COUNT(), it's time to call in the subqueries:

SET @rank := 0;
SELECT 
    @rank := @rank + 1 AS row_num, 
    sq.*
FROM (
    SELECT column_name, COUNT(*) AS count
    FROM orders_table
    GROUP BY column_name
    ORDER BY COUNT(*) DESC
) AS sq;


Secrets for speed runners

Use subqueries for accurate grouping and ordering. It's like prepping your vegetables before cooking. You line up your onions (data) in the right order and slice 'em (group by and order) the way you want before throwing them in the frying pan (outer SELECT statement).

Leaning into MySQL 8.0+

For the lucky ones who are in the future with MySQL 8.0 or later, you can use ROW_NUMBER() along with PARTITION BY to reset row number for each category:

SELECT 
  ROW_NUMBER() OVER (PARTITION BY category_column ORDER BY order_column) AS row_num,
  other_columns
FROM 
  your_table;


Think of it like resetting your lap counter in a race each time you complete a lap (partition).

Tricks to keep up your sleeve

Two things: First, for faster pagination, have @rank start from a specific offset for continuous rankings. Second, think about a permanent ranking column if you've got a simple table. Keep in mind, though, it could get cranky when data changes, and you'll need to soothe it with triggers or events.