I would like to select records and determine rank number for each similar data.
My data is as follows.
MEMBER ID | LOAN AMOUNT
1 | 2,000.00
2 | 1,000.00
3 | 4,000.00
4 | 1,000.00
The result I wanted is shown below.
RANK|MEMBER ID|LOAN AMOUNT
1 |3 |4,000.00
2 |1 |2,000.00
3 |2 |1,000.00
3 |4 |1,000.00
RANK is a new column. I am using MS SQL server 2008 and created a view table as shown below but it does not resulting to what is wanted.
select rank=count(*), s1.MemberID, s1.Loan_Amount
from (select MemberID, Loan_Amount from vwPrintTop20Borrowers) s1
group by s1.MemberID, s1.LOAN_AMOUNT
order by rank, s1.Loan_amount DESC
Please help. Thanks! :)
Just use RANK()
:
SELECT RANK() OVER(ORDER BY t.loan_amount DESC) as [rank],
t.memeber_id,t.loan_amount
FROM YourTable t
Although this will result in gaps E.G.
RANK | AMOUNT
1 10
2 9
3 8
3 8
5 7
To avoid that, for SQL-Server 2008+ , use DENSE_RANK()
DENSE_RANK() OVER(ORDER BY t.loan_amount DESC) as [rank]