MySQL select first occurrence group by event

Question

I have a list of musical events from different singers, each singer has different concerts, i need to extract the 'next event' in such a way that for every singer I choose the next occurrence and only the next one

for example for this table

id | singerId | occurrence
1  |     1    | 2017-01-01
2  |     1    | 2017-02-01
3  |     2    | 2017-03-01
4  |     2    | 2017-04-01

result should be

id | singerId | occurrence
1  |     1    | 2017-01-01
3  |     2    | 2017-03-01

I've tried with

select distinct * from table 
where occurrence > '2016-12-12 22:10:00' 
group by singerId 
order by occurrence

but somehow I am not getting the next occurrence in all cases, sometimes an occurrence is one in the middle, not the next one

'next' is the closest to 2016-12-12 22:10:00 greater than it

What is wrong in my query?

How do you defined "next"? 2017-01-01 is in the past. – shmosel Jan 03 '17 at 20:48 — shmosel, Jan 03 '17 at 20:48
next occurrence with respect to what? – Vamsi Prabhala Jan 03 '17 at 20:48 — Vamsi Prabhala, Jan 03 '17 at 20:48

score 2 · Accepted Answer · answered Jan 03 '17 at 20:54

2

Based on what you are saying, try this. Very vague on "next occurrence" definition.

select id, singerId, Min(Occurrence) Occurrence
from table 
where occurrence > '2016-12-12 22:10:00' 
group by id, singerId 
order by occurrence

answered Jan 03 '17 at 20:54

SS_DBA

2,403
1
11
15

MySQL select first occurrence group by event

1 Answers1