Replicate Row Number with older version of MYSQL

Question

Is there a nice way in MySQL (older version) to replicate the SQL Server function ROW_NUMBER()?

I am trying to find 2 most used categories with their usage count per day using this query but ROW_NUMBER and PARTITION are not available in my old version of SQL.

Select a.* 
FROM
( SELECT 
     dDay,
     category,
     COUNT(*) as CountOfCategory,
     ROW_NUMBER() OVER (PARTITION BY dDay ORDER BY COUNT(*) DESC) AS intRow
  FROM Table1
  GROUP BY category, dDate ) as a
WHERE intRow <= 2
ORDER BY dDay,
         intRow;

There is a variation of this question here but COUNT(*) in my query does not seem to fit the solutions there.

Input

dDay	Category
1	car
2	bike
2	car
1	car
3	truck
1	bike
1	car
3	car
3	car
2	bike
1	bike
2	truck
2	truck
2	truck

Expected Output: Top 2 categories (with their total count) per day

dDay	Category	CountOfCategory
1	car	3
1	bike	2
2	bike	2
2	truck	2
3	car	2
3	truck	1

Please provide sample data, expected output and specific MySQL version. — FanoFN, Jun 10 '21 at 08:32
*For example* This is wrong example. Synthactically wrong. If you'll disable ONLY_FULL_GROUP_BY it become logically wrong. — Akina, Jun 10 '21 at 08:45

Gordon Linoff · Accepted Answer · 2021-06-10T11:39:36.987

I am trying to find 2 most used categories with their usage count per day using this query but ROW_NUMBER and PARTITION are not available in my old version of SQL.

In older versions of MySQL, I think the simplest solution is to put the categories in a single column, using two levels of aggregation:

SELECT dDay,
       SUBSTRING_INDEX(GROUP_CONCAT(category, ':', CountOfCategory ORDER BY CountOfCategory DESC), ',', 2)
FROM (SELECT dDay, category, COUNT(*) as CountOfCategory,
      FROM Table1
      GROUP BY category, dDate
     ) cd
GROUP BY dDay;

Hopefully, this will be sufficient until you are able to upgrade to a more modern version of MySQL.

EDIT:

You can actually do what you want using variables:

SELECT cd.*
FROM (SELECT cd.*,
             (@rn := if(@d = dDay, @rn + 1,
                        if(@d := dDay, 1, 1)
                       )
             ) as rn
      FROM (SELECT dDay, category, COUNT(*) as CountOfCategory,
            FROM Table1
            GROUP BY category, dDate
            ORDER BY dDate, COUNT(*) DESC
           ) cd CROSS JOIN
           (SELECT @d := -1, @rn := 0) params
      ) cd
WHERE rn <= 2
ORDER BY dDate, rn;

Thank you so much for these solutions. Exactly what I was looking for. — MIRAU, Jun 13 '21 at 07:32

Replicate Row Number with older version of MYSQL

1 Answers1