Try:
SELECT * FROM MyTable ORDER BY Amount DESC
The only reason that might not give you what you want is if you are storing
Amount
in a VARCHAR or NVARCHAR column - in which case the comparison will be string based instead of numeric, and the whole comparison will be based on teh first different character. So your sort order would become
1
10
11
12
...
18
19
2
20
21
...
If you are, then change your DB to use a numeric column instead.
[edit]
Do you mean:
SELECT ID, Name, Amount, RANK () OVER (ORDER BY Amount DESC) as Rank
FROM MyTable
ORDER BY Amount DESC
Which will give you:
ID Name Amount Rank
1 AAA 5000 1
4 SSS 5000 1
2 BBB 4999 3
3 CCC 1500 4
Or:
SELECT ID, Name, a.Amount, b.Rank
FROM MyTable a
JOIN (SELECT Amount, RANK () OVER (ORDER BY Amount DESC) as Rank
FROM (SELECT DISTINCT Amount FROM MyTable)x) b ON a.Amount = b.Amount
ORDER BY a.Amount DESC
Which will give you:
ID Name Amount Rank
1 AAA 5000 1
4 SSS 5000 1
2 BBB 4999 2
3 CCC 1500 3
[/edit]