Ranking a list of numbers that may have repeating digits

Question

I am looking for a way to rank the very first change in a sequence of numbers within a list that may contain duplicates... example...

rec#  number
1     328
2     32C
3     32C
4     328
5     328

What I am looking for is to find the first 328 as #1... then the first 32C as #2... then the next occurring 328 as #3... and so on... the end result would look something like this

rec#  Number  rank
1     328     1
2     32C     2
4     328     3

I figured out a way to do it in Excel then bring it back into SQL...and was wondering if there is something like it in SQL... I am using SQL2000 so no ranking or partition available...

Thanks

sarin · Answer 1 · 2014-04-09T08:46:01.853

If you make the assumption that rec# is consecutive and unbroken then you can do something like the following. Let me explain:

SELECT t1.[rec#] AS rec1,
t2.[rec#] AS rec2,
t1.Number AS Num1,
t2.Number AS Num2,
CASE WHEN t2.Number IS NULL THEN 1
     WHEN t1.Number = t2.Number THEN 1
     ELSE 0
END AS MATCH
FROM Test t1
LEFT JOIN Test t2 ON t1.[rec#] = t2.[rec#] + 1

the left join with the +1 is the key bit allowing you to join the table back on itself but shifted one row. We can then compare each row with the previous to see if its matched.

Gives you:

t1.rec#  t1.number  t2.rec#  t2.number  Match
1        328        Null     Null       1
2        32C        1        328        0
3        32C        2        32C        1
4        328        3        32C        0
5        328        4        328        1

so...

You can then say

SELECT 
   CASE
      WHEN x.rec2 IS NULL THEN x.rec1
      ELSE x.rec2
   END AS [rec#],
   x.Num1 AS Number,
IDENTITY(INT, 1,1) AS Rank
INTO #Ranks

FROM (
    SELECT t1.[rec#] AS rec1,
    t2.[rec#] AS rec2,
    t1.Number AS Num1,
    t2.Number AS Num2,
    CASE WHEN t2.Number IS NULL THEN 1
         WHEN t1.Number = t2.Number THEN 1
         ELSE 0
    END AS MATCH
    FROM Test t1
    LEFT JOIN Test t2 ON t1.[rec#] = t2.[rec#] + 1
) x
WHERE x.MATCH = 1

SELECT * FROM #Ranks

I've used a Temporary table as a cheap way to add on the Rank value. There are other ways of doing this but at 01:05 that's the best you are going to get.

I've done a SQL Fiddle (in 2008 but without any 2008 features so should work in 2000) here: Enjoy

Ranking a list of numbers that may have repeating digits

1 Answers1