CODEWITHSUNDEEP
sqlsql-server
user2463014
user2463014

Reputation: 23

Rank with current order

I have table like this:

col1     | col2   
__________________
15077244 | 544648 
15077320 | 544648
15080285 | 544632
15382858 | 544648
15584221 | 544648
15584222 | 544648
15584223 | 544628
15584224 | 544628
15584225 | 544628
15584226 | 544628
15584227 | 544632
15584228 | 544632

And I want to rank it as the col2 value changed as in example below (This one is that I want to achieve):

col1     | col2   | rank 
________________________
15077244 | 544648 | 1
15077320 | 544648 | 1
15080285 | 544632 | 2
15382858 | 544648 | 1
15584221 | 544648 | 1
15584222 | 544648 | 1
15584223 | 544628 | 3
15584224 | 544628 | 3
15584225 | 544628 | 3
15584226 | 544628 | 3
15584227 | 544632 | 2
15584228 | 544632 | 2

I found an answer that suggest me to use DENSE_RANK() function. So I use it:

SELECT col1, col2, DENSE_RANK() OVER(ORDER BY col2) as rank     
FROM myTable

but when I use it it change the order of col1, like this:

col1      | col2   |   rank
____________________________
15584223  | 544628 |    1
15584224  | 544628 |    1
15584225  | 544628 |    1
15584226  | 544628 |    1
15080285  | 544632 |    2
15584227  | 544632 |    2
15584228  | 544632 |    2
15077244  | 544648 |    3
15077320  | 544648 |    3
15382858  | 544648 |    3
15584221  | 544648 |    3
15584222  | 544648 |    3

Now when I use ORDER BY at the end of my SELECT query like ORDER BY col1, I have data with correct order but RANKS are wrong, becouse for example col2 value 544648 has RANK 3 but it should have RANK 1.

How to use DENSE_RANK function or something different that helps me RANK my col2 values without changing an data order?

Upvotes: 2

Views: 77

Answers (3)

EzLo
EzLo

Reputation: 14189

You can use a correlated subquery that with a windowed minimum.

;WITH CorrelatedDistinctCount AS
(
    SELECT
        D.col1,
        D.col2,
        (
            SELECT 
                COUNT(DISTINCT(X.col2))
            FROM
                Data X
            WHERE
                X.col1 <= D.col1) AS DistinctCol2Count
    FROM
        Data D
)
SELECT
    C.col1,
    C.col2,
    MIN(C.DistinctCol2Count) OVER (PARTITION BY C.col2) AS rank
FROM
    CorrelatedDistinctCount C
ORDER BY
    C.col1 ASC

Upvotes: 0

SQLChao
SQLChao

Reputation: 7847

You need to change your order for dense_rank to desc. And order the results by col1 asc.

Fiddle Demo

SELECT 
    col1
  , col2
  , DENSE_RANK() OVER(ORDER BY col2 DESC) as rank     
FROM myTable
ORDER BY col1 ASC

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

While there may be an easier solution, here's one approach using a subquery with row_number to establish a grouping of results, ordering by min(col1):

SELECT t.col1, t.col2, t2.rank     
FROM myTable t JOIN (
  SELECT MIN(col1) minCol1, col2, ROW_NUMBER() OVER (ORDER BY MIN(col1)) rank
  FROM myTable
  GROUP BY col2
  ) t2 ON t.col2 = t2.col2
ORDER BY t.col1

Upvotes: 0

Related Questions