WHERE city = 'San Francisco'

AND prcp > 0.0;

-- You can request that the results of a query be returned in sorted order:

SELECT * FROM weather

ORDER BY city, temp_lo;

-- Here is a more complicated one. Duplicates are removed when DISTINCT is

-- specified. ORDER BY specifies the column to sort on. (Just to make sure the

-- following won't confuse you, DISTINCT and ORDER BY can be used separately.)

-- table name. If you want to be clear, you can do the following. They give

-- identical results, of course.

SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location

SELECT weather.city, weather.temp_lo, weather.temp_hi,

weather.prcp, weather.date, cities.location

FROM weather JOIN cities ON weather.city = cities.name;

-- Old join syntax

-- Suppose we want to find all the records that are in the temperature range

-- of other records. w1 and w2 are aliases for weather.

SELECT w1.city, w1.temp_lo, w1.temp_hi,

w2.city, w2.temp_lo, w2.temp_hi

SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,

w2.city, w2.temp_lo AS low, w2.temp_hi AS high

FROM weather w1 JOIN weather w2

ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

-- Aggregate with GROUP BY

SELECT city, max(temp_lo)

SELECT city,count(*),max(temp_lo)

FROM weather

GROUP BY city;

-- ... and HAVING

SELECT city, max(temp_lo)

SELECT city,count(*),max(temp_lo)

FROM weather

GROUP BY city

HAVING max(temp_lo) < 40;

-- We can filter rows before aggregating them:

SELECT city, count(*), max(temp_lo)

FROM weather

WHERE city LIKE 'S%'

GROUP BY city;

-- Another way is the FILTER clause, which operates per-aggregate:

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)

FROM weather

GROUP BY city;

-----------------------------

-- Updates: