This example goes through the steps for writing a SQL query that finds the top city in each region that is based on annual sales totals.
SELECT *, RANK() OVER (ORDER BY amt DESC) AS ranking FROM sales_tbl
WHERE region = 'Northeast' or region = 'Central' ORDER BY amt DESC;
city | state | region | quarter | amt | ranking
-----------+-------+-----------+---------+------+---------
New York | NY | Northeast | 4 | 5000 | 1
New York | NY | Northeast | 3 | 4300 | 2
New York | NY | Northeast | 2 | 3700 | 3
New York | NY | Northeast | 1 | 3000 | 4
Baltimore | MD | Central | 2 | 2500 | 5
Baltimore | MD | Central | 4 | 2500 | 5
Boston | MA | Northeast | 4 | 2400 | 7
Boston | MA | Northeast | 1 | 2000 | 8
Baltimore | MD | Central | 1 | 2000 | 8
Baltimore | MD | Central | 3 | 2000 | 8
Atlanta | GA | Central | 4 | 1700 | 11
Boston | MA | Northeast | 3 | 1700 | 11
Atlanta | GA | Central | 3 | 1600 | 13
Boston | MA | Northeast | 2 | 1500 | 14
Atlanta | GA | Central | 2 | 1500 | 14
Atlanta | GA | Central | 1 | 1500 | 14
(16 rows)
SELECT *, RANK() OVER (ORDER BY amt DESC) AS ranking FROM sales_tbl
WHERE ranking <= 2;
ERROR: Aggregates not allowed in WHERE clause
SELECT * FROM ( SELECT *, RANK() OVER (ORDER BY amt DESC) AS ranking
FROM sales_tbl WHERE region = 'Northeast' or region = 'Central') AS
subset WHERE ranking <= 2;
CITY | STATE | REGION | QUARTER | AMT | PROFIT_MARGIN | RANKING
---------+-------+-----------+---------+------+---------------+---------
New York | NY | Northeast | 4 | 5000 | 20 | 1
New York | NY | Northeast | 3 | 4300 | 22 | 2
(2 rows)
SELECT city, state, region, SUM(amt) AS yr_sales FROM sales_tbl WHERE
region = 'Northeast' or region = 'Central' GROUP BY region, state,
city;
CITY | STATE | REGION | YR_SALES
----------+-------+-----------+----------
Boston | MA | Northeast | 7600
New York | NY | Northeast | 16000
Baltimore | MD | Central | 9000
Atlanta | GA | Central | 6300
(4 rows)
SELECT city, state, region, SUM(amt) AS yr_sales, RANK() OVER
(PARTITION BY region ORDER BY SUM(amt)) FROM sales_tbl WHERE region =
'Northeast' or region = 'Central' GROUP BY region, state, city;
CITY | STATE | REGION | YR_SALES | RANK
----------+-------+-----------+----------+------
Boston | MA | Northeast | 7600 | 1
New York | NY | Northeast | 16000 | 2
Atlanta | GA | Central | 6300 | 1
Baltimore | MD | Central | 9000 | 2
(4 rows)
When a window aggregate and a GROUP BY clause display in a single query, the GROUP BY is evaluated first, and the window aggregate function is evaluated on the result of the GROUP BY.
SELECT * FROM (SELECT city, state, region, SUM(amt) AS yr_sales, RANK
() OVER (PARTITION BY region ORDER BY SUM(amt) DESC) AS ranking FROM
sales_tbl WHERE region = 'Northeast' or region = 'Central' GROUP BY
region, state, city) subset WHERE ranking = 1 ORDER BY yr_sales DESC;
city | state | region | yr_sales | ranking
----------+-------+-----------+----------+---------
New York | NY | Northeast | 16000 | 1
Baltimore | MD | Central | 9000 | 1
(2 rows)