Hi,
I have this working SQL Server query:
Code:
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:
Code:
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