PostgreSQL - Assigning window function to alias

Question

I'm trying to set ROW_NUMBER()... as an alias so I can reference it in the OFFSET. e.g. OFFSET some_alias - 1. I need to get a single row including the ROW_NUMBER() from a larger query. Here's my working code (gets correct ROW_NUMBER(), but isn't offset by the right amount):

WITH FirstQuery AS (
    SELECT "RepInitials", COUNT("OrderStatus"), ROW_NUMBER()OVER(ORDER BY COUNT("OrderStatus") DESC)
    FROM "tblBulkSalesQuery"
    WHERE "OrderStatus" = 'CMC'
    GROUP BY "RepInitials"
)
SELECT "RepInitials", COUNT("OrderStatus"), ROW_NUMBER()OVER(ORDER BY COUNT("OrderStatus") DESC)
    FROM "tblBulkSalesQuery"
    WHERE "OrderStatus" = 'CMC'
    GROUP BY "RepInitials"
    LIMIT 1
    OFFSET 1;

score 2 · Accepted Answer · 2012-11-26 15:14:54Z

2

select *
from (
    SELECT "RepInitials", 
           COUNT("OrderStatus") as order_status_count, 
           ROW_NUMBER() OVER (ORDER BY COUNT("OrderStatus") DESC) as rn
    FROM "tblBulkSalesQuery"
    WHERE "OrderStatus" = 'CMC'
    GROUP BY "RepInitials"
) as t
where rn = 1

Edit:

The t is an alias for the nested select ("derived table"). PostgreSQL requires each derived table to get it's own "name" and that can only be done by assigning a alias.

It's pretty much the same as:

with t as (
  ... here goes the real select ...
)
select *
from t 
where rn = 1;

edited Nov 26, 2012 at 15:14

answered Nov 26, 2012 at 14:00

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

ktross Over a year ago

I'm honestly not sure either. I really need to start learning more about SQL. Thanks so much for your quick response! :)

ktross Over a year ago

Your answer worked perfectly, but would you mind clarifying something for me? What does the t after the from mean?

Erwin Brandstetter Over a year ago

@ktross: One could be more verbose and write AS t which is recommended for column aliases but not necessary for table aliases.

user330315 Over a year ago

@ErwinBrandstetter: good point. Although I always do it for columns for some strange reason I never do it for derived tables ;)

Collectives™ on Stack Overflow

PostgreSQL - Assigning window function to alias

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related