CODEWITHSUNDEEP
mysqlsqlrow-number
csharpvsto
csharpvsto

Reputation: 89

Repeat Row Number with join

I am trying to get the row number for each increment ID by CUSTOMER_EMAIL.

STAGE_MAGENTO_ORDER TABLE:

CUSTOMER_EMAIL INCREMENT_ID ENTITY_ID
[email protected] 1232 24323
[email protected] 1258 25343

STAGE_MAGENTO_ORDER_LINEITEM TABLE:

PRODUCT_NAME ORDER_ID
Apple 24323
Banana 24323
Pear 25343

Desired Result (Order_NUMBER is the same for each Increment_ID):

| CUSTOMER_EMAIL|INCREMENT_ID | ENTITY_ID | PRODUCT_NAME | ORDER_NUMBER |
|---------------|-------------| ----------|--------------| ------------ |
|[email protected]  | 1232        |     24323 |Apple         |  1           |
|[email protected]  | 1232        |     24323 |Banana        |  1           |
|[email protected]  | 1258        |     25343 | Pear         |  2           |

Result from trying below (As you can see, the ORDER_NUMBER has increased when it shouldn't):

|CUSTOMER_EMAIL|INCREMENT_ID|ENTITY_ID|PRODUCT_NAME|ORDER_NUMBER|
|--------------|------------|---------|------------|------------|
|[email protected] | 1232       |   24323 |     Apple  |          1 |
|[email protected] | 1232       |   24323 |     Banana |          2 |
|[email protected] | 1258       |   25343 |      Pear  |          3 |

Code tried:


    SELECT 
        o.CUSTOMER_EMAIL
        , O.INCREMENT_ID
        , l.PRODUCT_NAME
        , ROW_NUMBER() OVER(PARTITION BY LOWER(o.customer_email) ORDER BY o.CREATED_AT) AS 
    ORDER_NUMBER
        , o.BASE_GRAND_TOTAL - o.BASE_SHIPPING_AMOUNT - o.BASE_TAX_AMOUNT AS NET_SALE
    FROM STAGE_MAGENTO_ORDER o
    INNER JOIN STAGE_MAGENTO_ORDER_LINEITEM l
    ON l.ORDER_ID = o.ENTITY_ID

Do I really have to change the to be this? (as I thought it wasn't good to do nested SELECT statements):


    SELECT 
        o.CUSTOMER_EMAIL
        , O.INCREMENT_ID
        , l.PRODUCT_NAME
        , o.ORDER_NUMBER
        , o.BASE_GRAND_TOTAL - o.BASE_SHIPPING_AMOUNT - o.BASE_TAX_AMOUNT AS NET_SALE
    FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY LOWER(customer_email) ORDER BY CREATED_AT) AS 
    ORDER_NUMBER FROM STAGE_MAGENTO_ORDER) o
    INNER JOIN STAGE_MAGENTO_ORDER_LINEITEM l
    ON l.ORDER_ID = o.ENTITY_ID

Upvotes: 0

Views: 223

Answers (1)

LukStorms
LukStorms

Reputation: 29667

Calculating a ROW_NUMBER, RANK or DENSE_RANK in a sub-query is mostly done when you want to filter on it.

In this case a DENSE_RANK would assign the same number for same values.

SELECT 
  o.CUSTOMER_EMAIL
, o.INCREMENT_ID
, o.ENTITY_ID
, l.PRODUCT_NAME
, DENSE_RANK() OVER (PARTITION BY LOWER(o.CUSTOMER_EMAIL)
                     ORDER BY o.INCREMENT_ID) AS ORDER_NUMBER
FROM STAGE_MAGENTO_ORDER o
JOIN STAGE_MAGENTO_ORDER_LINEITEM l
  ON l.ORDER_ID = o.ENTITY_ID
CUSTOMER_EMAIL INCREMENT_ID ENTITY_ID PRODUCT_NAME ORDER_NUMBER
[email protected] 1232 24323 Apple 1
[email protected] 1232 24323 Banana 1
[email protected] 1258 25343 Pear 2

Demo on db<>fiddle here

Upvotes: 1

Related Questions