Limit to two results for each individual ID

Question

How would I return two results for each individual meeting.id?

I've tried things like Row_Count() and Rank() but they seem to cause syntax errors.

This is my query which I need adapting to show the two results per meeting.id.

    SELECT meeting_appointment.* FROM `meeting` 
    INNER JOIN meeting_appointment ON ( 
        meeting_appointment.meeting_id = meeting.id AND meeting_appointment.pupil_id = 0 AND meeting_appointment.guardian_id = 0 AND meeting_appointment.deleted = 0 
    ) 
    WHERE ( 
        meeting.grade_id = "-1" OR meeting.grade_id IN ('87') 
    ) 
    AND meeting.startTime < '2016-10-06 14:00:00' + INTERVAL 1 HOUR AND meeting.startTime > '2016-10-06 14:00:00' - INTERVAL 1 HOUR

    GROUP by meeting_appointment.id 
    ORDER BY meeting_appointment.startTime ASC

You may need to use a `UNION` to do this. – CptMisery Oct 12 '16 at 14:40 — CptMisery, Oct 12 '16 at 14:40

score 1 · Answer 1 · answered Oct 12 '16 at 19:12

1

You don't have an aggregate function, so GROUP BY is redundant. Remove that, and add a LIMIT clause, and you should be good to go:

SELECT ma.*
FROM meeting m
INNER JOIN meeting_appointment ma ON ma.meeting_id = m.id
WHERE m.grade_id IN ('-1','87')
AND ma.pupil_id = 0
AND ma.guardian_id = 0
AND ma.deleted = 0 
AND m.startTime < '2016-10-06 14:00:00' + INTERVAL 1 HOUR
AND m.startTime > '2016-10-06 14:00:00' - INTERVAL 1 HOUR
ORDER BY ma.startTime ASC
LIMIT 2;

You could also use BETWEEN for the datetime column.

answered Oct 12 '16 at 19:12

Eugene

1,539
12
20

1

The above query will limit the overall result by 2. I doubt the OP has asked to limit results for each individual meeting.id by 2. – Kiran Muralee Oct 12 '16 at 19:54
I see the question now. I think the problem is then similar to http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql and http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results... So now I am thinking, how big are the result sets going to be? If it's not too large, you're going to get a big speedup by simply getting the required IDs and doing something with them... might be useful to understand how this'll be used in practice. – Eugene Oct 12 '16 at 20:16
@Eugene - The stackoverflow link you provided me with the answer I needed. I'm going to use `UNION_ALL` – YaBCK Oct 13 '16 at 07:59

Jayvee · Accepted Answer · 2016-10-13T09:32:17.223

If your meeting_appointment.id are consecutive then this should show the first two meeting_appointment.id per meeting.id:

SELECT meeting_appointment.* FROM `meeting` 
INNER JOIN meeting_appointment ON (meeting_appointment.meeting_id = meeting.id)

WHERE (meeting.grade_id = "-1" OR meeting.grade_id IN ('87')) 
AND meeting.startTime < '2016-10-06 14:00:00' + INTERVAL 1 HOUR AND meeting.startTime > '2016-10-06 14:00:00' - INTERVAL 1 HOUR
AND meeting_appointment.pupil_id = 0 
AND meeting_appointment.guardian_id = 0 
AND meeting_appointment.deleted = 0 
AND NOT EXISTS 
(SELECT 1 FROM meeting_appointment ma WHERE ma.id<meeting_appointment.id-1
    AND ma.pupil_id = 0 
    AND ma.guardian_id = 0 
    AND ma.deleted = 0
)
GROUP by meeting_appointment.id 
ORDER BY meeting_appointment.startTime ASC

If you meeting_appointment.id are just random or non-numeric then it might need some tweaks but in order to code those teaks we'll need to know a bit more about the schema.

I get error saying "#1054 - Unknown column 'ma' in 'where clause'" — YaBCK, Oct 13 '16 at 07:54

Limit to two results for each individual ID

2 Answers2