MySQL Convert ROW_NUMBER() OVER PARTITION

Question

I'm trying to convert an MSSQL database to MySQL and my version is 5.7. I have hit a roadblock.

SELECT orderid, invs.[InvoiceID],  invs.[InvoiceDate],
invs.[InvoiceNumber], invs.[HasClientPaid],
ROW_NUMBER() OVER (PARTITION by orderid,invs.invoicenumber,HasClientpaid ORDER BY orderid) AS DistNum 
FROM InvoiceLineItems Ilt 
JOIN Invoices Invs ON Ilt.InvoiceID= invs.InvoiceID

Any help would be greatly appreciated. Thank you

Paul Maxwell · Accepted Answer · 2019-12-09T20:10:32.963

2

MySQL will commence to suport window functions such as row_number() in version 8.x (not yet production ready as at Oct 29 2017), until then using @variables is a technique to mimic the effect:

SELECT
      @row_num :=IF(@prev_value=concat_ws('',orderid, invs.invoicenumber, HasClientpaid),@row_num+1,1)AS RowNumber
    , orderid
    , invs.[InvoiceID]
    , invs.[InvoiceDate]
    , invs.[InvoiceNumber]
    , invs.[HasClientPaid]
    , @prev_value := concat_ws('',orderid, invs.invoicenumber, HasClientpaid)
FROM InvoiceLineItems Ilt
JOIN Invoices Invs ON Ilt.InvoiceID = invs.InvoiceID
CROSS JOIN (SELECT @row_num :=1,  @prev_value :=0) vars
ORDER BY
      orderid, invs.invoicenumber, HasClientpaid
;

You need to concatenate the 3 fields orderid, invs.invoicenumber, HasClientpaid to mimic your original partitioning, and the ordering will need to be by those 3 columns as well. The ORDER BY is essential for this to work, if you need some other final ordering use the above as a subquery.

edited Dec 09 '19 at 20:10

answered Oct 29 '17 at 21:08

Paul Maxwell

33,002
3
32
51

why have that cross join? I know that subquery initializes those row_num and prev_value variables but why do it with a cross join? – Code_Jamer Jul 18 '21 at 04:54
1

The cross join simply adds the 2 new columns. Also note because it only has one row the cross join does not increase the total number of rows. – Paul Maxwell Jul 18 '21 at 07:09
1

Thanks Paul, much appreciated! – Code_Jamer Jul 18 '21 at 21:02
By the way, in many older examples of this MySQL technique you may find that the subquery for the extra columns is just listed in the from clause but separated by a comma with no join conditions for that subquery in the where clause. That has the same effect as an explicit cross join, but I do NOT recommend mixing that ancient syntax with explicit joins. Far better IMHO to be explicit and show that one is using a cross join. – Paul Maxwell Jul 19 '21 at 00:29
Very valid point! – Code_Jamer Jul 19 '21 at 06:01

MySQL Convert ROW_NUMBER() OVER PARTITION

1 Answers1

Linked