Returning highest Rank/Row_Number over Partition.

[Robeen]

Hi,

I have this working SQL Server query:

SELECT Top 100 XNUM, SerialNo, Amount, Rank() Over(Partition By XNum Order By Serialno Desc) As MostRecent 
From TableName 

What I'd like to do is add a where clause & have the query return only rows which have a Rank (MostRecent) of 1.

I tried adding "Where MostRecent = 1" but that doesn't work.

I have put the above SQL in a CTE and made it work but I would rather find a way of doing it as it would make it easier for me to use it as part of a bigger query.
I have also been able to make it work like this:

Select * From
(
SELECT Top 100 XNUM, SerialNo, Amount, Rank() Over(Partition By XNum Order By Serialno Desc) As MostRecent 
From TableName 
)
Where MostRecent = 1

Thanks!!

Robeen

[June7]

Review http://allenbrowne.com/ranking.html

Note what Allen says about limitations of ranking in query.