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

MySQL MIN/MAX Select based on different criteria in a single select statement

In mySQL, have a large table of history data and I am looking for an elegant way to be able to select the earliest/latest event types in a single select statement without grouping by Item and Event type and running multiple select statemements if possible as there are quite a few event types to consider. e.g from data like the below:

ID ITEM EVENT DATE
1 a Turn on 10/09/2023
2 a Turn on 11/09/2023
3 a Update 11/09/2023
4 a Restart 12/09/2023
5 a Turn on 10/09/2023
6 a Turn on 12/09/2023
7 a Restart 10/09/2023
8 a Turn on 11/09/2023
9 a Update 11/09/2023
10 a Update 12/09/2023

In order to return the first of each event type per item, I was hoping I could do something clever and neat along the lines of this if possible?

Select ID, 
MIN(Date WHERE event = 'Turn On')
MIN(Date WHERE event = 'Update')
MIN(Date WHERE event = 'Restart')
GROUP BY Item

If not the only way I can think of is to group by item and event, select the minumum for each event type and join these all together but was wondering if there is a simpler way?

Tags:

2

Answers


  1. 0

    Just as when pivoting, use a CASE expression inside the aggregate.

    Select 
    MIN(CASE WHEN event = 'Turn On' THEN Date END) AS Turn_On
    MIN(CASE WHEN event = 'Update' THEN Date END) AS Update
    MIN(CASE WHEN event = 'Restart' THEN Date END) AS Restart
FROM yourTable

    There’s no point in selecting the ID column, since the ID is different for each minimum.

    Login or Signup to reply.
  2. 0

    you need always a GROUP BY if you want tp select , multiple items.

    The solution Barmar provided will only function when sql_mode=only_full_group_by iws disabled

    CREATE TABLE tab
    (`ID` int, `ITEM` varchar(1), `EVENT` varchar(7), `DATE` datetime)
;
    
INSERT INTO tab
    (`ID`, `ITEM`, `EVENT`, `DATE`)
VALUES
    (1, 'a', 'Turn on', '2023-10-09 02:00:00'),
    (2, 'a', 'Turn on', '2023-11-09 01:00:00'),
    (3, 'a', 'Update', '2023-11-09 01:00:00'),
    (4, 'a', 'Restart', '2023-12-09 01:00:00'),
    (5, 'a', 'Turn on', '2023-10-09 02:00:00'),
    (6, 'a', 'Turn on', '2023-12-09 01:00:00'),
    (7, 'a', 'Restart', '2023-10-09 02:00:00'),
    (8, 'a', 'Turn on', '2023-11-09 01:00:00'),
    (9, 'a', 'Update', '2023-11-09 01:00:00'),
    (10, 'a', 'Update', '2023-12-09 01:00:00')
;


    Records: 10  Duplicates: 0  Warnings: 0

    SELECT 
     `ITEM`
  , MIN(CASE WHEN `EVENT` = 'Turn on' THEN `DATE` END) as 'Turn on'
  , MIN(CASE WHEN `EVENT` = 'Update' THEN `DATE` END) as 'Update'
  , MIN(CASE WHEN `EVENT` = 'Restart' THEN `DATE` END) as 'Restart'

FROM tab 
GROUP BY ITEM
    ITEM Turn on Update Restart
    a 2023-10-09 02:00:00 2023-11-09 01:00:00 2023-10-09 02:00:00 
    SELECT 
     `ITEM`
  , MIN(CASE WHEN `EVENT` = 'Turn on' THEN `DATE` END) as 'Turn on'
  , MIN(CASE WHEN `EVENT` = 'Update' THEN `DATE` END) as 'Update'
  , MIN(CASE WHEN `EVENT` = 'Restart' THEN `DATE` END) as 'Restart'

FROM tab 
  WHERE item = 'A'
GROUP BY ITEM
    ITEM Turn on Update Restart
    a 2023-10-09 02:00:00 2023-11-09 01:00:00 2023-10-09 02:00:00 
    SELECT 
     `ITEM`
  , MIN(CASE WHEN `EVENT` = 'Turn on' THEN `DATE` END) as 'Turn on'
  , MIN(CASE WHEN `EVENT` = 'Update' THEN `DATE` END) as 'Update'
  , MIN(CASE WHEN `EVENT` = 'Restart' THEN `DATE` END) as 'Restart'

FROM tab 


    In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'fiddle.tab.ITEM'; this is incompatible with sql_mode=only_full_group_by

    fiddle

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

Back To Top
Search