Alias to rank not able to use in where clause

Question

create table tab1(sno int, name varchar(30), age int);

insert into tab1 values(1, 'abc1', 22);
insert into tab1 values(2, 'abc2', 23);
insert into tab1 values(3, 'xyz', 28);
insert into tab1 values(4, 'abc3', 26);
insert into tab1 values(5, 'abc4', 25);

select sno, name, age, rank() over (order by sno) as ranking from tab1 where
ranking = trunc((select count(*)/2 from tab1)) + 1; //This query is giving error

Error is ORA-00904: "RANKING": invalid identifier

score 5 · Accepted Answer · answered Nov 05 '15 at 15:08

You are trying to filter the results of your select based on value that is calculated after all filtering is done. You need to change it to something like this:

select * from (
  select sno, name, age, rank() over (order by sno) as ranking from tab1 
) where ranking = trunc((select count(*)/2 from tab1)) + 1;

score 2 · Answer 2 · answered Nov 05 '15 at 15:06

Your problem is thank you define an alias in the select and then you want to filter by it. You need to use either a CTE or subquery:

with cte as (
      select sno, name, age, rank() over (order by sno) as ranking
      from tab1
     )
select cte.*
from cte
where cte.ranking = trunc((select count(*)/2 from tab1)) + 1;

You seem to want to calculate the median value. I would definitely not recommend rank() for this purpose, because of the way that it treats ties. A better alternative is row_number(), so this is pretty close to getting the median. And, you don't need the subquery:

with cte as (
      select sno, name, age, row_number() over (order by sno) as ranking,
             count(*) over () as cnt
      from tab1
     )
select cte.*
from cte
where 2*cte.ranking in (cnt, cnt + 1)

This works well enough for both even and odd numbers of rows.

You might also be interested in the MEDIAN(), PERCENTILE_DISC(), and PERCENTILE_CONT() functions.

Alias to rank not able to use in where clause

2 Answers2