First ranked per group in SQL

Example table

create table weather_reports
(
	location character varying(50),
	time timestamp with time zone,
	report character varying(50)
);

insert into weather_reports values ('Graz', current_timestamp, '9,4 °C, cloudy, 3 km/h SE');
insert into weather_reports values ('Leoben', current_timestamp, '7,0 °C, rain, 0 km/h S');

insert into weather_reports values ('Graz', current_timestamp, '9,5 °C, cloudy, 4 km/h SE');
insert into weather_reports values ('Leoben', current_timestamp, '6,7 °C, rain, 2 km/h S');

Solutions

SQL:2003 way with window function

select *
from (
	select location, time, report, row_number() over (partition by location order by time desc) "time_rank"
	from weather_reports
) cte
where 1=1
	and cte.time_rank = 1;

PostgreSQL way with DISTINCT ON extension

select DISTINCT ON (location) location, time, report
from weather_reports
ORDER BY location, time DESC;

Source

Differneces

• Syntax of PostgreSQL's extension is easier to comprehend and write
• What about performance?