1

In MySQL 8.0, you can use a great function called ROW_NUMBER().

However, after creating a database query that works nicely in MySQL 8.0 that uses ROW_NUMBER(), I see that the server that I'm using has MySQL 5.7 and I was told I can't upgrade it to MySQL 8.0.

Are there any easy ways to translate ROW_NUMBER() from MySQL 8.0 into syntax with the same functionality for MySQL 5.7, specifically for my case? I checked a few other translations and for my case and they didn't work.

My query is below and here is a link to the fiddle for a demo

SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY website ORDER BY id ASC) AS r_num
FROM sites
) AS t
WHERE t.r_num < 4
ORDER BY t.datePub ASC;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Jimmison Johnson
  • 41
  • 5
  • Take a look: https://stackoverflow.com/questions/54239851/mysql-5-7-getting-the-row-number – tttony Nov 28 '22 at 02:24
  • I looked into that and a simple plug and chug for my case caused the error 'ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OVER(PARTITION BY website ORDER BY id ASC) AS r_num FROM sites ) AS t WHERE t.r_' at line 4' – Jimmison Johnson Nov 28 '22 at 02:26
  • I linked to a previous answer of mine that shows two methods to do this type of query in MySQL 5.x, as well as the window function solution in MySQL 8.0. The solution in that answer is for the newest 4 rows per group, but you want the oldest 4 rows per group. But it demonstrates the technique. You just have to reverse the order. – Bill Karwin Nov 28 '22 at 16:08

1 Answers1

0
  1. Create a temp table that extracts all rows WHERE t.r_num < 4.
  2. Add a new column to the temp table.
  3. Populate the new column with sequential integers.
  4. Revise the SELECT to rely on the temp table.
J_H
  • 17,926
  • 4
  • 24
  • 44