SQL Server 2005 - CROSS APPLY Example

In some situation you need to write a query like
"Get top X records for each Y'. For instance:
Return top 3 recent orders for each customer.

For this task we can use the Cross Apply operator that
available from SQL Server 2005.

In the following example we have a table that holds accidents
by roads and we need to return the 2 most recent accidents
for each road.

CREATE TABLE #RoadAccidents
(
AccidentID INT NOT NULL,
RoadID INT NOT NULL,
AccidentDate DATETIME NOT NULL,
)
INSERT INTO #RoadAccidents(AccidentID,RoadID,AccidentDate)
SELECT 1,111,GETDATE()
UNION ALL
SELECT 2,111,GETDATE()+1
UNION ALL
SELECT 3,111,GETDATE()-2
UNION ALL
SELECT 4,222,GETDATE()-3
UNION ALL
SELECT 5,222,GETDATE()+4
UNION ALL
SELECT 6,222,GETDATE()-5
UNION ALL
SELECT 7,333,GETDATE()
UNION ALL
SELECT 8,333,GETDATE()-1.5
UNION ALL
SELECT 9,333,GETDATE()-2
GO
SELECT *
FROM #RoadAccidents



SELECT DISTINCT CrossApResults.*
FROM #RoadAccidents AS RdAccidents CROSS APPLY
(
SELECT TOP (2) RdAccidents2.RoadID,RdAccidents2.AccidentID,RdAccidents2.AccidentDate
FROM #RoadAccidents AS RdAccidents2
WHERE RdAccidents2.RoadID=RdAccidents.RoadID
ORDER BY AccidentDate DESC
) AS CrossApResults
ORDER BY CrossApResults.RoadID,CrossApResults.AccidentDate DESC