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: 
In most databases, you can also do this with windows functions, by doing something like:

```
select c.*
from (select cg.*,
             max(time) over (partition by city) as maxtime,
             min(time) over (partition by city) as mintime
      from c2g
     ) c
where time = maxtime or time = mintime

```

(This definitely doesn't work in mysql or Access, since they do not support windows functions.)
```
SELECT City, MAX(Time)
FROM cg
GROUP BY City
UNION
SELECT City, MIN(Time)
FROM cg
GROUP BY City

```