-2

This is my SQL query:

SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY RequestID ORDER BY CriticalAssessmentID DESC) AS rowid
FROM
    ApplicationCriticalityAssessment

This works perfectly fine and gives me incremental rowid's like 1, 2,3, 4 etc. for the same requestID.

When I want to select only rowid=1

SELECT
    *, 
    ROW_NUMBER() OVER (PARTITION BY RequestID ORDER BY CriticalAssessmentID DESC) AS rowid
FROM
    ApplicationCriticalityAssessment  
WHERE
    rowid = 1

Now this falters with an error

Invalid column name rowid.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Venkat
  • 1,702
  • 2
  • 27
  • 47

2 Answers2

3

You can't use column aliases in the where clause. One approach is to wrap it in another query:

SELECT *
FROM   (SELECT *, 
               ROW_NUMBER() over (PARTITION BY RequestID
                                  ORDER BY CriticalAssessmentID DESC) 
                  AS rowid
        FROM   ApplicationCriticalityAssessment) t
WHERE  rowid = 1
Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

You can try using a subquery or Cte

select * from (

select *, ROW_NUMBER() over (partition by RequestID order by CriticalAssessmentID desc) as rowid
from ApplicationCriticalityAssessment  

)a
where  rowid=1
abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20