Click here to Skip to main content
14,732,809 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
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

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
   
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
   
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]
   
v3
Comments
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
   
Answer updated

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900