CODEWITHSUNDEEP
sql-server
aggaton
aggaton

Reputation: 3350

Generate row numbers sorted by values in virtual/constructed column

I am trying to generate row numbers sorted by values in a constructed column, however I keep getting an error:

Invalid column name 'Type'.

SELECT  ROW_NUMBER() OVER (ORDER BY [Type] ASC) As [RowID], [ID], 
   case when ([CaseOne] = 1) then 1 else case when ([Label] IS NOT NULL) then 2 else 3 end end AS [Type]
FROM [Table1]

Using 2008 R2.

Upvotes: 2

Views: 73

Answers (1)

JohnS
JohnS

Reputation: 2052

You need to put your CASE statement into the ORDER BY. You can't use an ALIASed column in an ORDER BY or HAVING clause:

SELECT
  ROW_NUMBER() OVER ( ORDER BY CASE WHEN ( [CaseOne] = 1 ) THEN 1
                                    ELSE CASE WHEN ( [Label] IS NOT NULL )
                                              THEN 2
                                              ELSE 3
                                         END
                               END ASC ) AS [RowID],
  [ID],
  CASE WHEN ( [CaseOne] = 1 ) THEN 1
       ELSE CASE WHEN ( [Label] IS NOT NULL ) THEN 2
                 ELSE 3
            END
  END AS [Type]
FROM
  [Table1]

Upvotes: 2

Related Questions