Search code examples
sql-server-2016rankrow-numberdense-rank

DENSE_RANK or ROW by AccountNo not quite right

Need to create a row or ranking to repeat by account. (See below for desired result)

Tried Various DENSE_RANKS, RANK, ROW_NUMBER but keep getting incorrect values!

  , DENSE_RANK() OVER ( ORDER BY a.accountNo )
  , DENSE_RANK() over (partition by a.accountNo order by (SELECT NULL) ASC) dr1
  , ROW_NUMBER() OVER(ORDER BY  a.accountNo ASC) AS Ro
  , RANK() OVER (ORDER BY a.accountNo DESC) AS xRank

So the data would appear like below:

Account | Rnk
12345   | 1
12345   | 2
12345   | 3
23456   | 1
23456   | 2
23456   | 3
23456   | 4

I know I'm missing something simple...

Solution

  • Assuming arbitrary order of row numbers, you can do this:

    , ROW_NUMBER() OVER(PARTITION BY a.accountNo ORDER BY @@SPID) As Rn

    @@SPID Returns the session ID of the current user process, which means it's a fixed value within the query - so the order is actually arbitrary (and it's shorter than writing (SELECT NULL)).