Ranking dupes

Question

Ranking dupes

mhg1063

Say Hey Kid

Points: 682
More actions
June 11, 2006 at 7:32 am

#172900

Is there a function in SQL Server 2000 or tsql way to do the following
Loan_id    Note_rate     Rank
1234                10.7           1
1234                10.5          2
5678                10.5           1
1345                10.0           1
1435                10.5          1
1435                10.5           2
1435                10.0           3

I want to I guest rank the records with in each unique loan_id

Thanks in advance for any assistance.

.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

The problem with these types of groupings is trying to get the ranking to change where there is duplicate information, otherwise, the dupes will have the same rank. The only way to do it (that I know of) without a cursor or WHILE loop is to have a unique column and that column is in order by the anticipated ranking... not a scenario that usually occurs in nature, so we have to trick nature a bit. Or course, some of the new ranking functions in SQL Server 2005 do this stuff without much hassle but you're using SQL Server 2000...

So... let's setup a test table to simulate your data, move it to a known sorted state in a temp table, and solve the problem...

--===== If temporary test table exists, drop it
     IF OBJECT_ID('TempDB..#Loans') IS NOT NULL
        DROP TABLE #Loans

--===== If temporary work table exists, drop it
     IF OBJECT_ID('TempDB..#Work') IS NOT NULL
        DROP TABLE #Work
GO

--===== Create the temporary test table to simulate the user's table
     -- Don't know what the primary key is but I assume you have one.
 CREATE TABLE #Loans 
        (
         Loan_ID   INT,
         Note_rate DECIMAL (6,1)
        )

--===== Populate the table with sample data including a couple dupe loans
 INSERT INTO #Loans (Loan_id, Note_rate)
 SELECT 1234,10.7  UNION ALL
 SELECT 1234,10.5  UNION ALL
 SELECT 5678,10.5  UNION ALL
 SELECT 1345,10.0  UNION ALL
 SELECT 1435,10.5  UNION ALL
 SELECT 1435,10.5  UNION ALL
 SELECT 1435,10.0

--===== Create and populate a work table on the fly
     -- If this becomes huge, you could add a primary key
 SELECT IDENTITY(INT,1,1) AS RowNum,
        Loan_id,
        Note_rate
   INTO #Work
   FROM #Loans
  ORDER BY Loan_ID,Note_Rate

--========================================================================
--===== Test setup complete... begin the demo for the problem solution
--========================================================================

--===== Do the ranking 
 SELECT Loan_id, Note_rate, 
        (SELECT COUNT(*) 
           FROM #Work t2 
          WHERE t2.Loan_id = t1.Loan_id
            AND t2.Note_rate >=  t1.Note_rate
            AND t2.RowNum    >=  t1.RowNum
        ) AS Rank
   FROM #Work t1
  ORDER BY Loan_id,Rank

Here's the output...

Loan_id     Note_rate Rank        
----------- --------- ----------- 
1234        10.7      1
1234        10.5      2
1345        10.0      1
1435        10.5      1
1435        10.5      2
1435        10.0      3
5678        10.5      1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mhg1063 Say Hey Kid Points: 682 More actions · Answer 2

Thanks Jeff Moden for the highly detailed answer. This is exactly what I needed. I really appreciate the input.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

You bet... thank you for the feedback...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)