Mind Gravy

So you think you can Box?

Posted by Steve on August 26th, 2008

Words…should have sent a poet…

Return the Earliest Date Record in SQL Server 2005

Posted by Steve on July 15th, 2008

I’ve been struggling with a query lately and I haven’t been able to find a good answer.

What I’m trying to find is the earliest record in a detail table, then return all the master and detail records associated with it, but only that single “detail” record.

Here is the table schema:

Table Structure

So the query I am trying to write is trying to get all distinct Order Detail items, but only the earliest record of the Order Header.

When the query is executed as run above, here are the results:

sqlQuery2

However, I only want to return the earliest OrderDate Header record, so the CSR will display as well.

Here is the required resultset:

Name	TotalAmount	CSR	OrderDate	ItemName	Quantity
Fatty McGhee	322	Someone Else	2008-05-01	Widget 1	40
Fatty McGhee	322	Someone Else	2008-05-01	Widget 2	34
Fatty McGhee	322	Someone Else	2008-05-01	Widget 3	20
Fatty McGhee	322	Someone Else	2008-05-01	Widget 4	30
Fatty McGhee	343	Capt. No Normalization	2008-06-01	Widget 5	34

I would like this to be a single view/query and not a stored procedure due to customer requirements. Any help with this would be greatly appreciated.

UPDATE:

I thought I would try to explain this a little better. So I want to get information about the order detail item, “Widget 1″. I only want to return this record once (distinct), and I want it to include the earliest SINGLE record from the Order Header table.

If I try to do a group by to get the MIN(OrderDate) and try to return CSR in the result set, it will be incorrect and return 2 Order Header records because CSR is different:

Name	TotalAmount	CSR	OrderDate	ItemName	Quantity
Fatty McGhee	322	Someone Else	2008-05-01	Widget 1	40
Fatty McGhee	343	Capt. No Normalization	2008-06-01	Widget 1	50

So, why not do a “PARTITION BY”, RANK and add a WHERE clause returning only where RANK = 1? Because of the size of the order table, returning the partitioned table before getting the RANK of the table without filtering down the results first by User takes too long. Because I want to filter out for a single user, I would need to pass a variable into the subquery, MyResults before running the sub query.

SELECT [Name], [TotalAmount], [CSR], [OrderDate], [ItemName],
  [Quantity]
FROM
(
    SELECT U.[Name], OH.[TotalAmount], OH.[CSR], OH.[OrderDate],
      OD.ItemName, OD.Quantity,
      RANK() OVER (PARTITION BY OD.[ItemName]       ORDER BY OrderDate) AS RANK
    FROM OrderDetail AS OD
    INNER JOIN [OrderHeader] AS OH
    ON OD.[OrderID] = OH.[ID]
    INNER JOIN [Users] AS U
    ON OH.UserID = U.ID
) AS MyResults
WHERE RANK = 1
ORDER BY [OrderDate]