0

I'm migrating my database from MSSQL to MySQL. AFAIK, before version 8, MySQL doesn't support ROW_NUMBER like in MSSQL. How should I do the migration for the following query.

SELECT
  A.Att1,
  B.Att2,
  ...
  SeqNr = ROW_NUMBER() OVER (PARTITION BY A.ID, B.AnotherID ORDER BY C.SomeAttribute DESC),
FROM TableOne A
INNER JOIN TableTwo B
  ON A.ID = B.ID
INNER JOIN TableThree C
  ON B.ID = C.ID

The ROW_NUMBER() OVER (PARTITION...ORDER BY) part should work out of the box for MySQL 8+, but I'm currently using 5.7, what is the best alternative?

Thanks.

tab87vn
  • 389
  • 5
  • 17
  • Does this answer your question? [ROW\_NUMBER() in MySQL](https://stackoverflow.com/questions/1895110/row-number-in-mysql) – Serg Mar 25 '21 at 15:00

2 Answers2

0

Use variables:

SELECT abc.*,
       (@rn := IF(CONCAT_WS(':', A.id, B.AnotherID) = @ii, @rn + 1,
                  IF(@ii := CONCAT_WS(':', A.id, B.AnotherID), 1, 1)
                 )
       ) as seqnum
FROM (SELECT . . .
      FROM TableOne A JOIN
           TableTwo B
           ON A.ID = B.ID JOIN
           TableThree C
           ON B.ID = C.ID
      ORDER BY A.id, B.AnotherID, C.SomeAttribute DESC
     ) abc CROSS JOIN
     (SELECT @ii := '', @rn := 0) params;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

MYSQL also support row_number(). There are no much differenced between mysql and mssql. Please refer to the link below. There are information related to this. https://www.educba.com/mysql-row_number/

Gudwlk
  • 1,177
  • 11
  • 11