RANK

[Maqza via AccessMonster.com]

Hi!
Please help me how to write Rank in SQL,

Code Date YTD Rank
220-00 20-Oct-06 25.25% 4
220-01 20-Oct-06 23.72% 5
220-02 20-Oct-06 34.65% 1
220-03 20-Oct-06 32.11% 2
220-04 20-Oct-06 18.40% 9
220-05 20-Oct-06 26.51% 3
220-06 20-Oct-06 21.88% 6
220-07 20-Oct-06 14.48% 10
220-08 20-Oct-06 19.90% 7
220-09 20-Oct-06 18.80% 8

YTD: current price/ytdprice -1
20-Oct-06 current price
31-Dec-05 YTD price

 

[John Spencer]

An SQL statement to do this would look like the following.

SELECT Code, [Date], YTD
, 1 + (SELECT Count(*)
FROM YourTable as Temp
WHERE Temp.YTD < YourTable.YTD) as Rank
FROM YourTable