I have a MySQL table like follows, where each row represents an event for a class. Each class can have from 1 to N events.

I need to retrieve two events per class: the first one and the last one. In case there's a single event for a class it is enough to retrieve it once, but twice would be awesome. For the previous example, the expected result for the requested query would be:

Suppose my table is called events, how could I get it with a single query? I'm just a newbie with SQL and have no idea of how to do it or if it is even possible.

CodePudding user response：

You can use window functions for making kind of task efficient.

Specifically the ROW_NUMBER window function will allow you to assign an incremental numeric by ordering on your date ascendently (first time) and descendently (last time), given the "classid" partition. In this way, your first and last value will have "ROW_NUMBER = 1" for the ascendent and descendent generated fields respectively.

WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY classId ORDER BY date_ ASC) AS rn_asc,
           ROW_NUMBER() OVER(PARTITION BY classId ORDER BY date_ DESC) AS rn_desc
    FROM tab
)
SELECT classId, date_, message
FROM cte
WHERE rn_asc = 1 OR rn_desc = 1

Check the demo here.

CodePudding user response：

With a left join, you could actually have the first and last in the same row.

select joined.classId, first_events.message as first_message, last_events.message as last_message from
(select classId, MAX(date) as max_date, MIN(date) as min_date
from events 
group by classId) as joined
left join events first_events on joined.classId = first_events.classId and joined.min_date = first_events.date
left join events last_events on joined.classId = last_events.classId and joined.max_date = last_events.date