SQL: Use a calculated fields from the SELECT in the WHERE clause

Question

I have a SQL query that does some ranking, like this:

SELECT RANK() OVER(PARTITION BY XXX ORDER BY yyy,zzz,oooo) as ranking, * 
FROM SomeTable
WHERE ranking = 1 --> this is not possible

I want to use that ranking in a WHERE condition at the end.

Now I nest this query in another query and do filtering on the ranking there, but is there no easier or faster way to filter on such values from the SELECT statement?

Answer 1

Use a CTE (Common Table Expression) - sort of an "inline" view just for the next statement:

;WITH MyCTE AS
(
    SELECT 
        RANK() OVER(PARTITION BY XXX ORDER BY yyy,zzz,oooo) as ranking, 
        * 
    FROM SomeTable
) 
SELECT * 
FROM MyCTE
WHERE ranking = 1 --> this is now possible!

Answer 2

Sorry for the former posting, i forgot : windowing functions can only be used in select or order by clauses. You'll have to use a sub query:

SELECT * FROM 
(
  SELECT RANK() OVER(PARTITION BY XXX ORDER BY yyy,zzz,oooo) as ranking, * 
  FROM SomeTable
) t
WHERE ranking = 1

OR A CTE.