wiltomap wiltomap - 4 months ago 11
SQL Question

PostgreSQL query including WITH subquery

From the following table (named

status
), I need to extract town codes having a status of
'01'
by the end of year 2015. The column
status_date
stores the date at which a town changed its status.

gid | town | status | status_date
-----+-----------+---------+-------------
1 | 86001 | 00 | 2000-01-01
2 | 86001 | 01 | 2016-03-01
3 | 86002 | 01 | 2000-01-01
4 | 86003 | 00 | 2000-01-01
5 | 86003 | 01 | 2015-03-01
6 | 86003 | 02 | 2015-09-01


I can achieve that with the following query which is a bit long:

WITH tab AS (SELECT town, MAX(status_date) FROM status GROUP BY town)

SELECT
t.town

FROM tab t
LEFT JOIN status s ON t.town = s.town AND t.max = s.status_date

WHERE t.max < '2016-01-01' AND s.status = '01' ;


The result is:

town
-------
86002


Any idea on how to make this query simpler? Is the
WITH
essential?




To create the table for testing:

CREATE TABLE status (gid serial NOT NULL, town CHARACTER VARYING(5), status CHARACTER VARYING(2), status_date DATE) ;

INSERT INTO status (town, status, status_date) VALUES
('86001', '00', '2000-01-01'),
('86001', '01', '2016-03-01'),
('86002', '01', '2000-01-01'),
('86003', '00', '2000-01-01'),
('86003', '01', '2015-03-01'),
('86003', '02', '2015-09-01') ;

Answer

You can do this with distinct on:

select s.*
from (select distinct on (s.town) s.*
      from status s
      where s.status_date < '2016-01-01'
      order by s.town, s.status_date
     ) s
where status = '01';

This query will get the latest status for each town up to the end of 2015. The outer query then selects those that are 01.