SQL: Use a calculated fields from the SELECT in th

问题:

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?

回答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!

回答2:

select * from (
select RANK() OVER(PARTITION BY name ORDER BY id) as ranking, *  
from PostTypes
) A
where A.ranking = 1

http://data.stackexchange.com/stackoverflow/query/edit/59515

回答3:

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.