CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Erik Dekker
Erik Dekker

Reputation: 2433

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

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?

Upvotes: 4

Views: 6393

Answers (3)

bpgergo
bpgergo

Reputation: 16047

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

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

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25397

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.

Upvotes: 1

marc_s
marc_s

Reputation: 755321

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!

Upvotes: 8

Related Questions