SQL SELECT earliest last update of each unique product and only filled orders with quantity of 1-CodePudding

I need to make a query to select the first order filled of each unique product so if I have three orders for apples 2 orders for bananas and 5 orders for oranges I need to get the first order filled for each apple, banana, and orange from the table 'Orders'

I tried multiple things but this was my best effort

SELECT  [orderID]
      ,[productname]
      ,[orderStatus]
      ,[customerID]
      ,[lastUpdateTime]
     FROM [Orders]
     Where lastUpdateTime > cast( getdate() as date )
      and (orderStatus = 'filled')
      and (productname =     UNIQUE)
    order by lastUpdateTime;

expecting to get three rows, one for each productname in order of lastupdatetime, each with all that orders information but got an error 'Incorrect syntax near the keyword 'UNIQUE''

CodePudding user response：

Here is another similar question I answered. Please read that for the how and why, but I will adjust so you can see how it applies to your request.

WITH QryEachProduct AS (
SELECT
      o.OrderID,
      o.ProductName,
      o.OrderStatus,
      o.CustomerID,
      o.LastUpdateTime,
      ROW_NUMBER() OVER (PARTITION BY o.ProductName
                            ORDER BY o.LastUpdateTime) row_num    
   FROM 
      Orders o
) 
SELECT 
      Q.*
   FROM 
      QryEachProduct Q
   WHERE 
      Q.row_num = 1