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? — Amit
– Amit, Commented Dec 10, 2015 at 9:23

Mihai · Accepted Answer · 2015-12-10 09:27:47Z

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, 2015 at 9:27

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2015-12-10 09:27:53Z

3

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;

answered Dec 10, 2015 at 9:27

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

SQL limit by group by

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest