Geek Answers Handbook

PostgreSQL query including WITH with a subquery

In the following table (called status ) I need to display the city codes having the status '01' by the end of 2015. The status_date column stores the date the city 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 this 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' ;

Result:

 town ------- 86002

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

To create a 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') ;
+5
source share
3 answers

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 desc ) s where status = '01';

This request will receive the last status for each city until the end of 2015. The external query then selects those that are 01 .

+3
source

Your subquery contains the expression GROUP BY , whlist is an external query. Therefore you need to use a subquery.

You could embed this, but it will only make reading the query more difficult. Your request is as simple as it can get.

0
source

Your approach is correct - CTE related queries are more readable and, if properly built, they can provide performance benefits.

Instead of selecting a city in CTE, select the gid column. Then connect the source table with it and the veil:

 WITH tab AS ( SELECT gid, MAX(status_date) FROM status GROUP BY gid HAVING MAX(status_date) < '2016-01-01' ) SELECT s.whatever FROM tab t INNER JOIN status s ON t.gid = s.sid WHERE s.status = '01'

EDIT

My apologies; I was in a hurry this morning, so I wrote the request incorrectly. Now I had time to analyze the problem more deeply.

If performance is important, then work with it around a PC like this.

 WITH tab AS ( SELECT MAX(gid) as ID FROM [status] WHERE YEAR(status_date) = 2015 AND status = '01' GROUP BY town ) SELECT s.* FROM tab t INNER JOIN status s ON t.ID = s.gid

This only works if status_date grows along with gid . Otherwise, you need to return to the original request that you posted, and max on the date. However, you can / should use INNER JOIN instead of LEFT JOIN :

 WITH tab AS ( SELECT town, MAX(status_date) as Latest FROM [status] WHERE YEAR(status_date) = 2015 AND status = '01' GROUP BY town ) SELECT s.* FROM tab t INNER JOIN [status] s ON t.town = s.town AND t.Latest = s.status_date
0
source

More articles:


All Articles