Find the row associated with a Min/Max, without inner loop

Question

I have a question related to T-SQL and SQL Server. Let's say I have a table Orders with 2 columns: - ProductId int - CustomerId int - Date datetime I want the date of the first order for every product, so I perform this type of query: ``` SELECT ProductId, MIN(Date) AS FirstOrder FROM Orders GROUP BY ProductId ``` I have an index on `ProductId`, including the columns `CustomerId` and `Date` to speed up the query (`IX_Orders`). The query plan looks like a non-clustered index scan on `IX_Orders`, followed by a stream aggregate (no sort thanks to the index). Now my problem is that I also want to retrieve the `CustomerId` associated with the first order for each product (Product 26 was first ordered on Tuesday 25, by customer 12). The tricky part is that I don't want any inner loop in the execution plan, because it would mean an additional read per `ProductId` in the table, which is highly inefficient. This should just be possible using the same non-clustered index scan, followed by strea…

Accepted Answer

this will handle products that have duplicate dates:

```
DECLARE @Orders table (ProductId int
                      ,CustomerId int
                      ,Date datetime
                      )

INSERT INTO @Orders VALUES (1,1,'20090701')
INSERT INTO @Orders VALUES (2,1,'20090703')
INSERT INTO @Orders VALUES (3,1,'20090702')
INSERT INTO @Orders VALUES (1,2,'20090704')
INSERT INTO @Orders VALUES (4,2,'20090701')
INSERT INTO @Orders VALUES (1,3,'20090706')
INSERT INTO @Orders VALUES (2,3,'20090704')
INSERT INTO @Orders VALUES (4,3,'20090702')
INSERT INTO @Orders VALUES (5,5,'20090703')  --duplicate dates for product #5
INSERT INTO @Orders VALUES (5,1,'20090703')  --duplicate dates for product #5
INSERT INTO @Orders VALUES (5,5,'20090703')  --duplicate dates for product #5

;WITH MinOrders AS
(SELECT
     o.ProductId, o.CustomerId, o.Date
         ,row_number() over(partition by o.ProductId order by o.ProductId,o.CustomerId) AS RankValue
     FROM @Orders o
     INNER JOIN (SELECT
                     ProductId
                         ,MIN(Date) MinDate 
                     FROM @Orders 
                     GROUP BY ProductId
                ) dt ON o.ProductId=dt.ProductId AND o.Date=dt.MinDate
 )
SELECT
    m.ProductId, m.CustomerId, m.Date
    FROM MinOrders  m
    WHERE m.RankValue=1
    ORDER BY m.ProductId, m.CustomerId
```

this will return the same results, just use the same declare and inserts as the above code:

```
;WITH MinOrders AS
(SELECT
     o.ProductId, o.CustomerId, o.Date
         ,row_number() over(partition by o.ProductId order by o.ProductId,o.CustomerId) AS RankValue
     FROM @Orders o
 )
SELECT
    m.ProductId, m.CustomerId, m.Date
    FROM MinOrders  m
    WHERE m.RankValue=1
    ORDER BY m.ProductId, m.CustomerId
```

You can try out each version to see which will run faster...

Find the row associated with a Min/Max, without inner loop

Solution

Problem