14,732,809 members
See more:
i have this table

ID NAME AMOUNT
1 AAA 50000
2 BBB 49999
3 CCC 15001
4 DDD 28000
5 KKK 15000
6 EEE 28005
7 SSS 50000

i need ranking like this 50000 1st, 49999 2nd , 3rd 35000.. likewise increased amount will get highest rank .

What I have tried:

i have tried rank,dense_rank,ntile,row count
Posted
Updated 4-Jul-16 20:59pm
RossMW 4-Jul-16 0:59am

What wrong with a order statement or row_number see https://msdn.microsoft.com/en-nz/library/ms186734.aspx
Burning Thoughts 4-Jul-16 1:07am

values which are closer getting same rank
RossMW 4-Jul-16 1:23am

By definition that is correct, otherwise you will need a secondary unique Id field to seperate out the same values

## Solution 3

SELECT *, DENSE_RANK() OVER(ORDER BY amount DESC)
FROM Data
```1	AAA	50000	1
7	SSS	50000	1
2	BBB	49999	2
6	EEE	28005	3
4	DDD	28000	4
3	CCC	15001	5
5	KKK	15000	6```

hoặc

SELECT *, RANK() OVER(ORDER BY amount DESC)
FROM Data
```1	AAA	50000	1
7	SSS	50000	1
2	BBB	49999	3
6	EEE	28005	4
4	DDD	28000	5
3	CCC	15001	6
5	KKK	15000	7```

## Solution 2

If you want to add a rank, then first you need to select the `DISTINCT` records from the amount column

Then, from that `NESTED SELECT` you need to get the `ROW_NUMBER` when it's ordered by the amount, descending

Then with THAT query, you can do a `JOIN` on your original `SELECT`, using amount as the criteria

Best of luck

## Solution 1

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.

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]
v3
Burning Thoughts 4-Jul-16 1:27am

can you rank those amount based on highest amounts like below

50000 1st,
49999 2nd,
35000 3rd
RossMW 4-Jul-16 1:28am

That what the desc (descending) statement stand for.
Burning Thoughts 4-Jul-16 1:41am

this is what i am looking for
id name amount Rank
1 AAA 50000 1
7 SSS 50000 1
2 BBB 49999 2
6 EEE 28005 3
4 DDD 28000 4
3 CCC 15001 5
5 KKK 15000 6
OriginalGriff 4-Jul-16 4:33am