SQL limit by group by

Question

This is probably not very correct title of the question, but failed to figure out better name.

I have following table structure:

id | lat  | lon  | trajectory_id | time
1  | 15.8 | 17.1 | 162           | 10:01
2  | 11.5 | 59.7 | 162           | 10:02
3  | 16.4 | 79.9 | 162           | 10:03
4  | 29.5 | 10.3 | 180           | 11:12
5  | 58.2 | 11.1 | 180           | 11:13
6  | 54.5 | 14.1 | 180           | 11:14
7  | 14.9 | 15.2 | 166           | 13:40
8  | 15.0 | 13.1 | 166           | 13:42

And I would like to get first point of each trajectory (ordered by time). This means following:

id | lat  | lon  | trajectory_id | time
1  | 15.8 | 17.1 | 162           | 10:01
4  | 29.5 | 10.3 | 180           | 11:12
7  | 14.9 | 15.2 | 166           | 13:40

How can I do that? I tried to group by trajectory_id and then applying limit 1, but it limits trajectories, not points itself.

Possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Amit, Dec 10 '15 at 09:23

score 4 · Answer 1 · answered Dec 10 '15 at 09:27

4

WITH CTE AS
(SELECT id,lat,lon,trajectory_id,time,
ROW_NUMBER() OVER(PARTITION BY trajectory_id ORDER BY time) rn
FROM t)
SELECT id,lat,lon,trajectory_id,time FROM CTE WHERE rn=1

answered Dec 10 '15 at 09:27

Mihai

26,325
7
66
81

score 3 · Accepted Answer · answered Dec 10 '15 at 09:27

Use ROW_NUMBER() per trajectory_id in order to only retrieve the first row per trajectory_id.

select id, lat, lon, trajectory_id, time
from
(
  select 
    mytable.*, 
    row_number() over (partition by trajectory_id order by time) as rn
  from mytable
) numbered
where rn = 1;

SQL limit by group by

2 Answers2