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: 
Maybe over complicating things slightly or maybe a valid way of coding this with `CTE`:

[SQL FIDDLE EXAMPLE](http://sqlfiddle.com/#!3/059ff/4)

```
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 

```
```
SELECT City, MAX(Time)
FROM cg
GROUP BY City
UNION
SELECT City, MIN(Time)
FROM cg
GROUP BY City

```