View source for First ranked per group in SQL

== Example table ==

<pre>
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');
</pre>

== Solutions ==

=== SQL:2003 way with window function ===

<pre>
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;
</pre>

=== PostgreSQL way with DISTINCT ON extension ===

<pre>
select DISTINCT ON (location) location, time, report
from weather_reports
ORDER BY location, time DESC;
</pre>

[https://www.postgresql.org/docs/current/sql-select.html Source]

=== Differences ===

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

[[Category:SQL]]