Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the subquery with WHERE row_num < 3?

Query

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
    SELECT ROW_NUMBER() 
    OVER (
        PARTITION by loc.pid
        ORDER BY ST_Distance(r.the_geom, loc.the_geom)
    ) as row_num,
    loc.pid, loc.land_type, r.road_name, 
    ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
    FROM ch05.land AS loc
    LEFT JOIN ch05.road AS r
    ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
    WHERE loc.land_type = 'police station'
) AS foo

WHERE foo.row_num < 3
ORDER BY pid, row_num;

Query that does not work

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
    SELECT ROW_NUMBER() 
    OVER (
        PARTITION by loc.pid
        ORDER BY ST_Distance(r.the_geom, loc.the_geom)
    ) as row_num,
    loc.pid, loc.land_type, r.road_name, 
    ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
    FROM ch05.land AS loc
    LEFT JOIN ch05.road AS r
    ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
    WHERE loc.land_type = 'police station'
    AND row_num < 3
) AS foo

ORDER BY pid, row_num;

Error: ERROR: column "row_num" does not exist

share|improve this question
    
The first one is the only possible way (apart from using a CTE). It's a problem with the visibility, you cannot refer to a column alias at the same "level" where you define it. –  a_horse_with_no_name Mar 31 '13 at 21:08
    
It seems like we can define the alias loc using FROM ch05.land AS loc, then refer to it like WHERE loc.land_type = 'police station'. In this case is it referring to the alias at the same level it is defined on? –  Nyxynyx Mar 31 '13 at 21:34

1 Answer 1

up vote 1 down vote accepted

The column "row_num" doesn't exist because the logical order of processing requires the dbms to apply the WHERE clause before it evaluates the SELECT clause. The windowing function is part of the SELECT clause, so its alias isn't accessible in the same statement's WHERE clause.

The FROM clause is the very first part of the statement to be evaluated. That's why the aliases you declare in the FROM clause are accessible in the WHERE clause of the same statement.

Search this page for "logical processing order". Although this links to SQL Server documentation, the logical order of processing is the same for every dbms that complies with SQL standards.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.