Pick specific column value in a group of rows based on condition

  • I am working with mariadb server version 10.6.4.

    This is my table definition:

    CREATE TABLE `tmp_dba` (
      `ID` bigint(20) NOT NULL AUTO_INCREMENT,
      `case_id` bigint(20) DEFAULT NULL,
      `client_id` bigint(20) NOT NULL,
      `arrival` date DEFAULT NULL,
      `departure` date DEFAULT NULL,
      PRIMARY KEY (`ID`)

    Here is some example data:

    INSERT INTO `tmp_dba` VALUES

    What I would like to achieve is determine the MIN(arrival) of each group of case_id and client_id and in case departure is not null MAX(departure) should be displayed otherwise null.

    I want to end up with just one row per client providing the the data mentioned above.

    For example for case_id = 10 I want to see 1 row like that: 10;1000;2018-10-02;NULL.

    For case_id = 20 the result should be 4 rows, because of 4 different combinations of case_id and client_id.

    For case_id = 30 there should be 5 rows shown, because of 5 different combinations of case_id and client_id.

    I must be doing something wrong when using group by.

    Further Information:

    • Data is consecutive in the meaning that a new record entered will have a higher ID than the old one. Also a new record will be - relative to the one before - in the future, always. This does not mean that the next record always would have an arrival the day after last departure.
    • A new record in the table for the same customer will always set the departure date of the previous record. The previous record departure in that case can not be null.
    • When there is no departure and the client is "in" still, departure will be null always.
    • Departure must be less than arrival unless departure is null.
    • Arrival can not be null.

    Is this doable?


    -- First solution, but wrong, GROUP_CONCAT(ID) avoids error below
    -- ID, case_id, client_id, 

    Thank you very much for your help.


  • What code do you have so far? I assume you are using GROUP BY case_id, client_id.

    To discover whether any departure is NULL (across a group) is with COUNT(*) = COUNT(departure). This is because COUNT(*) counts all rows; the other counts only rows where departure IS NOT NULL. So, this is how you might make the departure expression:

    IF(COUNT(departure) = COUNT(*), MAX(departure), NULL)


    Your proposed solution has the "only_full_group_by" problem since it asks for multiple things. Change ID to GROUP_CONCAT(ID).

    Also, it would be a slight optimization to change the ORDER BY to match the GROUP BY.

Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2