CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlmaxdense-rank
user2964644
user2964644

Reputation: 189

Sequential number and maximum value using DENSE_RANK

I am using DENSE_RANK to assign a sequential number to different values of [y] within each group [x]. It adds a new column [rank].

I also want a new column showing the maximum rank [y] (highest sequence number) within each [x] e.g. [highest_rank]. Is there a way to obtain this new column at the same time, without having to create a separate table for maximum values then joining the tables together?

SELECT [x],[y],[rank]
INTO table2
FROM
(
SELECT *, DENSE_RANK() OVER (PARTITION BY [x] ORDER BY [y] ) AS [rank]
FROM table1
) tmp

Upvotes: 2

Views: 1957

Answers (1)

TT.
TT.

Reputation: 16137

If I understood you correctly, you're looking for something like this:

;WITH cte1 AS (
    SELECT *,DENSE_RANK() OVER (PARTITION BY [x] ORDER BY [y] ) AS [rank]
    FROM table1 
),
cte2 AS (
    SELECT [x], [highest_rank]=MAX([rank])
    FROM cte1
    GROUP BY [x]
)
SELECT cte1.[x],cte1.[y],cte1.[rank],cte2.[highest_rank]
FROM cte1 INNER JOIN cte2 ON cte2.[x]=cte1.[x];

Upvotes: 1

Related Questions