top 1 first_value() based on two criteria

Seko 0
Viewed 29

I'm still new at mssqlserver, i need to get the first order for the same customer made order in certain period based on the brand name and customer_id I used first_value() as sub query with top 1 however i got only the first value based on the brand regardless the customer id

what i got

Data Customer_ID Brand first_order
2022-01-01 1649104 PH 2020-09-26
2022-01-01 1306498 PH 2020-09-26
2022-01-01 1290371 PH 2020-09-26
2022-01-01 456756 TB 2020-11-09
2022-01-01 1823713 TB 2020-11-09
2022-01-01 2178025 BK 2020-08-12
2022-01-01 216435 BK 2020-08-12
2022-01-01 19031 BK 2020-08-12
2022-01-01 438095 BK 2020-08-12

the code I used

SELECT Data , Brand  ,  Customer_ID  ,
(select  top 1 first_value(H.Data) over (partition by Customer_ID , Brand order by H.Data ASC) 
from ORDER H
where h.Customer_ID = Customer_ID and H.Brand = Brand 
) as firts_order
from ORDER
LEFT OUTER JOIN ORDER_SOURCE SRC ON SRC.SRC_ID = ORDR_SOURCE

WHERE SRC_NAME IN ('SR 1' , 'SR 2') and DATE  = '2022-01-01'
sql-server
2 Answers

I've not used that first_value() window function before, but my bet is that you don't need a subquery. Instead:

SELECT Data, 
   Brand,  
   Customer_ID, 
   FIRST_VALUE(Data) OVER (PARTITION BY Customer_ID, Brand ORDER BY Data) as first_order
FROM ORDER
    LEFT OUTER JOIN ORDER_SOURCE SRC ON SRC.SRC_ID = ORDR_SOURCE
WHERE SRC_NAME IN ('SR 1' , 'SR 2') AND DATE  = '2022-01-01'

Alternatively you could use a correlated subquery INSTEAD. You don't need to combine this with a window function. Although, I wouldn't recommend it since the window function route would be quicker.

SELECT Data , Brand  ,  Customer_ID  ,
  (
    select top 1 H.data
    from ORDER H
    where H.Customer_ID = O.Customer_ID 
        and H.Brand = O.Brand 
    ORDER BY H.Data ASC
  ) as first_order
from ORDER O
LEFT OUTER JOIN ORDER_SOURCE SRC ON SRC.SRC_ID = ORDR_SOURCE    
WHERE SRC_NAME IN ('SR 1' , 'SR 2') and DATE  = '2022-01-01'
JNevill0

after working around I have found a more efficient solution for me using min() Instead of the first_value()

SELECT H.Data , H.Brand , H.Customer_ID , O.Data from ORDER H LEFT OUTER JOIN ORDER_SOURCE SRC ON SRC.SRC_ID = H.ORDR_SOURCE
LEFT OUTER JOIN ( select min(Data) AS Data, Brand, Customer_ID
from ORDER LEFT OUTER JOIN ORDER_SOURCE ON SRC_ID = ORDR_SOURCE
WHERE SRC_NAME IN ('SR 1' , 'SR 2') GROUP by Brand, Customer_ID ) AS O ON H.Brand = O.Brand AND H.Customer_ID = O.Customer_ID WHERE SRC_NAME IN ('SR 1' , 'SR 2') and DATE = '2022-01-01'

Seko0
Related