Subset for SELECT DISTINCT

Question

I have the following code to write the rows with min and max timestamp into a new table:

sql_sort_and_delete = """
                CREATE TABLE tmp AS
                SELECT DISTINCT * FROM c2g
                WHERE time = (SELECT max(time) FROM c2g)
                UNION SELECT DISTINCT * FROM c2g
                WHERE time = (SELECT min(time) FROM c2g);;"""

It works fine however there are subsets for different cities in the c2g table and I would like to change the code so that I get the distinct min / max result for each city. Is there an easy way to do this? Thanks!

Answer 1

Maybe over complicating things slightly or maybe a valid way of coding this with CTE:

SQL FIDDLE EXAMPLE

CREATE TABLE c2g 
    ( 
     CITY varchar(20), 
     TIME INT
    );

INSERT INTO c2g
(CITY, TIME)
VALUES
('A', 1),
('A', 2),
('B', 2),
('B', 2),
('B', 2),
('B', 2),
('C', 1),
('C', 2),
('C', 5),
('C', 20);

To get required records:

WITH CITY_cte(CITY, myMAX, myMIN)
AS
(
  SELECT
      CITY
      ,MAX(TIME) 
      ,MIN(TIME) 
  FROM c2g
  GROUP BY CITY
)
SELECT x.* 
FROM  
    c2g x
    INNER JOIN CITY_cte y
        ON x.CITY = y.CITY
WHERE 
  x.TIME = y.myMAX 
  OR
  x.TIME = y.myMIN 

Answer 2

To get the aggregate MIN()/MAX() per city, use a GROUP BY city. To pull the remaining columns from c2g, you then need to INNER JOIN the main table against that subquery on the city and time value.

SELECT DISTINCT c2g.*
FROM
  c2g
  INNER JOIN (SELECT city, MAX(time) AS time FROM c2g GROUP BY city) maxtime
    ON c2g.city = maxtime.city AND c2g.time = maxtime.time
UNION 
SELECT DISTINCT c2g.*
FROM
  c2g
  INNER JOIN (SELECT city, MIN(time) AS time FROM c2g GROUP BY city) mintime
    ON c2g.city = mintime.city AND c2g.time = mintime.time

I think this can be simplified and would actually work without the UNION by using an OR in the join's ON condition:

SELECT DISTINCT c2g.*
FROM
  c2g
  INNER JOIN (SELECT city, MAX(time) AS maxtime, MIN(time) AS mintime FROM c2g GROUP BY city) maxtime
    ON c2g.city = maxtime.city AND (c2g.time = maxtime.time OR c2g.time = maxtime.mintime)