How to rank highest amount to lowest amount

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 3-Jul-16 18:45pm

Burning Thoughts

Updated 4-Jul-16 19:59pm

Add a Solution

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

3 solutions

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

Posted 3-Jul-16 20:09pm

barneyman

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2016-07-03T19:19:00

Try:

SQL

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

If you are, then change your DB to use a numeric column instead.

[edit]
Do you mean:

SQL

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:

SQL

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]

Vũ Văn Huy · Accepted Answer · 2016-07-04T19:59:00

SELECT *, DENSE_RANK() OVER(ORDER BY amount DESC)
FROM Data

C#

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

C#

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

How to rank highest amount to lowest amount

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0