Doing FIRST and LAST aggregates in SQL Server 2005

Users of Microsoft Access may be familiar with the aggregation functions FIRST and LAST. Basically, what you want from these aggregates is to scan the tables in a sorted order. The first or last value encountered in each group is send to the output – much like the existing SQL Server MIN/MAX function.

Let me illustrate with some pseudo code:

 

SELECT Col

  , FIRST(Value) AS FirstV

  , LAST(Value) AS LastV

  , SUM(Value) AS SumV

FROM Table

GROUP BY Col

On a table with these rows:

Col	Value
A	3
A	2
A	6
B	5
B	1

…You want this output:

Col	FirstV	LastV	SumV
A	3	6	11
B	5	1	12

Notice how there in an implicit assumption of a row ordering. You probably want some sort of identity column to order your rows. Implicitly – this is a very ISAM way of looking at the world. Now, how do we do this inside SQL Server?

 

Lets make some test data based on AdventureWorksDW:

SELECT

s.*, IDENTITY(int, 1,1) AS ID

INTO

#Agg

FROM

dbo.FactInternetSales s

CROSS

JOIN (SELECT TOP 30 * FROM sys.objects) scaleUpFactor

 

CREATE

UNIQUE INDEX IX_FirstLast ON #Agg (ProductKey, ID, OrderQuantity)

 

Well… My colleage Jesper Rasmussion came up with a brilliant answer (try this on the testdata from above):

 

SELECT

I.ProductKey

  , F.OrderQuantity AS FirstQuantity

  , L.OrderQuantity AS LastQuantity

  , I.SumQuantity AS SumQuantity

FROM

  (SELECT ProductKey

   , min(ID) AS FirstID

   , max(ID) AS LastID

   , sum(OrderQuantity) AS SumQuantity

   FROM #Agg

   GROUP BY ProductKey) I

INNER

JOIN #Agg F ON F.ID = I.FirstID AND F.ProductKey = I.ProductKey

INNER

JOIN #Agg L ON L.ID = I.LastID AND L.ProductKey = I.ProductKey

 

This query has some very interesting properties. If you check the executions statistics you will see that SQL Server only does very few I/O requests in the index created (not more than 10-20% more than it takes for a full scan – probably around log(size(#agg) ). Since all rows HAVE to be visited to answer the requiest – this means that the query is very efficient.

 

Now, if only we could make a first and last aggregate that does just one table scan… 🙂