I was interested to see what the difference in performance between these queries as I would always have used Row_Number with a partition before.

On the Northwind database (SQL 2005) I ran the following 2 queries

select

a.ProductID

,a.UnitPrice

from

(select

ProductID

,UnitPrice

,Rank() over (order by ProductID) as Ranking

,Row_Number() over (order by ProductID,UnitPrice) as RowNumber

from

[Order Details]

) as a

where

a.Ranking=a.RowNumber

order by

a.ProductID

select

a.ProductID

,a.UnitPrice

from

(select

ProductID

,UnitPrice

,Row_Number() over (partition by ProductID order by UnitPrice) as RowNumber

from

[Order Details]

) as a

where

a.RowNumber=1

order by

a.ProductID

In the Actual Execution Plan, the first query showed up as 64% and the second as 36%.

Far from exhaustive, but I'll probably stick to Row_Number with Partition by.

In (partial) answer to Phil Wood, if all I wanted was the lowest price for each product then I would use grouping, I'd probably use this method if I wanted to find the customer or order date as well, changing the code to

select

a.ProductID

,a.UnitPrice

,b.CustomerID

,b.OrderDate

from

(select

ProductID

,UnitPrice

,OrderID

,Row_Number() over (partition by ProductID order by UnitPrice) as RowNumber

from

[Order Details]

) as a

inner join

Orders as b

on

a.OrderID=b.OrderID

where

a.RowNumber=1

order by

a.ProductID