Question 21830 in Mysql Question posted in
The official MySQL documentation can be found here.

Retrieve first and last events by category with SQL – Mysql

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.

classId date message
54 2020-08-31 08:24:04 …
54 2020-08-31 08:32:15 …
54 2020-08-31 08:32:31 …
54 2020-08-31 08:33:25 …
57 2020-08-31 09:02:47 …
57 2020-08-31 12:10:17 …
57 2020-08-31 14:15:11 …
81 2020-09-01 06:41:15 …
81 2020-09-01 06:41:16 …
… … …
81 2020-09-01 07:14:02 …

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:

classId date message
54 2020-08-31 08:24:04 …
54 2020-08-31 08:33:25 …
57 2020-08-31 09:02:47 …
57 2020-08-31 14:15:11 …
81 2020-09-01 06:41:15 …
81 2020-09-01 07:14:02 …

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search