CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Yogesh
Yogesh

Reputation: 3482

DENSE_RANK on Calculated Column

CASE 
          WHEN [col1] = 's' THEN 8
          WHEN [col1] = 't' THEN 6 
          WHEN [col1] = 'u' THEN 9
          WHEN [col2] = 'v' THEN 1
.......
END AS product,
DENSE_RANK() OVER (ORDER BY product ASC) AS [Rank]
FROM dbo.TableA

DENSE_RANK() is not working on calculated column Product, showing

Invalid column name 'product'.

How to apply DENSE_RANK() on product?

Upvotes: 1

Views: 880

Answers (1)

Mithrandir
Mithrandir

Reputation: 25337

You could do something like this:

SELECT 
  *, 
  DENSE_RANK() over (order by product)
FROM (
SELECT 
  CASE 
          WHEN [col1] = 's' THEN 8
          WHEN [col1] = 't' THEN 6 
          WHEN [col1] = 'u' THEN 9
          WHEN [col2] = 'v' THEN 1
 ...
END AS product
FROM dbo.TableA
) t

Upvotes: 3

Related Questions