Click here to Skip to main content
14,690,515 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

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
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
   
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
   

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