clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805426 fiddles created (40853 in the last week).

select version();
version()
10.3.32-MariaDB
 hidden batch(es)


CREATE TABLE tmp_dba ( ID INT NOT NULL PRIMARY KEY, case_id INT DEFAULT NULL, client_id INT NOT NULL, arrival DATE DEFAULT NULL, departure DATE DEFAULT NULL );
 hidden batch(es)


INSERT INTO tmp_dba VALUES (1, 10, 1000,'2018-10-02','2019-04-25'), (2, 10, 1000,'2019-04-26','2019-05-01'), (3, 10, 1000,'2019-05-02',NULL), (4, 20, 2000,'2018-11-21',NULL), (5, 20, 2001,'2018-11-21',NULL), (6, 20, 2002,'2018-11-21',NULL), (7, 30, 3000,'2019-03-04','2022-01-01'), (8, 30, 3001,'2019-04-04','2022-02-01'), (9, 30, 3002,'2019-05-04','2022-03-01'), (10, 30, 3003,'2019-06-04','2022-04-01'), (11, 30, 3004,'2019-07-04','2022-05-01');
 hidden batch(es)


-- -- Try seeing which queries will (and will not) run under different versions of -- PostgreSQL, MySQL and MariaDB. Google about ONLY_FULL_GROUP_BY! -- --
 hidden batch(es)


-- -- Works with all versions of PostgreSQL, MySQL and MariaDB! -- -- uses standard constructs! -- SELECT case_id, client_id, MIN(arrival) AS m_d, CASE WHEN MAX(COALESCE(departure, '2038-01-01')) = '2038-01-01' THEN NULL ELSE MAX(COALESCE(departure, '2038-01-01')) END AS m_d FROM tmp_dba GROUP BY case_id, client_id ORDER BY case_id, client_id;
case_id client_id m_d m_d
10 1000 2018-10-02
20 2000 2018-11-21
20 2001 2018-11-21
20 2002 2018-11-21
30 3000 2019-03-04 2022-01-01
30 3001 2019-04-04 2022-02-01
30 3002 2019-05-04 2022-03-01
30 3003 2019-06-04 2022-04-01
30 3004 2019-07-04 2022-05-01
 hidden batch(es)


SHOW VARIABLES LIKE 'sql_mode';
Variable_name Value
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 hidden batch(es)


SELECT ID, case_id, client_id, MIN(arrival) AS arrival, IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure -- non-standard! FROM tmp_dba GROUP BY case_id, client_id ORDER BY case_id;
ID case_id client_id arrival departure
1 10 1000 2018-10-02
4 20 2000 2018-11-21
5 20 2001 2018-11-21
6 20 2002 2018-11-21
7 30 3000 2019-03-04 2022-01-01
8 30 3001 2019-04-04 2022-02-01
9 30 3002 2019-05-04 2022-03-01
10 30 3003 2019-06-04 2022-04-01
11 30 3004 2019-07-04 2022-05-01
 hidden batch(es)


SHOW VARIABLES LIKE 'sql_mode'; -- This is MySQL/MariaDB specific!
Variable_name Value
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 hidden batch(es)


SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
 hidden batch(es)


SHOW VARIABLES LIKE 'sql_mode';
Variable_name Value
sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 hidden batch(es)


SELECT case_id, client_id, -- removed ID fields! MIN(arrival) AS arrival, IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure -- non-standard! FROM tmp_dba GROUP BY case_id, client_id ORDER BY case_id;
case_id client_id arrival departure
10 1000 2018-10-02
20 2000 2018-11-21
20 2001 2018-11-21
20 2002 2018-11-21
30 3000 2019-03-04 2022-01-01
30 3001 2019-04-04 2022-02-01
30 3002 2019-05-04 2022-03-01
30 3003 2019-06-04 2022-04-01
30 3004 2019-07-04 2022-05-01
 hidden batch(es)


-- -- Dodgy implementation of FIRST_VALUE/LAST_VALUE... in MariaDB. -- -- SELECT case_id, client_id, fva AS first_arrival, lvd AS last_departure FROM ( SELECT case_id, client_id, FIRST_VALUE(arrival) OVER (PARTITION BY case_id) AS fva, LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd FROM tmp_dba ORDER BY ID ) AS sq GROUP BY case_id, client_id, fva, lvd ORDER BY case_id, client_id;
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
 hidden batch(es)


SELECT case_id, client_id, arrival departure FROM tmp_dba ORDER BY case_id, client_id, departure
case_id client_id departure
10 1000 2018-10-02
10 1000 2019-04-26
10 1000 2019-05-02
20 2000 2018-11-21
20 2001 2018-11-21
20 2002 2018-11-21
30 3000 2019-03-04
30 3001 2019-04-04
30 3002 2019-05-04
30 3003 2019-06-04
30 3004 2019-07-04
 hidden batch(es)


SELECT *, FIRST_VALUE(arrival) OVER (PARTITION BY case_id) AS fva, LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd, LAST_VALUE ( CASE WHEN departure IS NULL THEN CAST('2038-01-01' AS DATE) ELSE departure END) OVER (PARTITION BY case_id) AS lvd_1, LAST_VALUE(departure) OVER ( PARTITION BY case_id ORDER BY CASE WHEN departure IS NULL THEN '2038-01-01' ELSE departure END ) AS lvd_2 FROM tmp_dba ORDER BY ID;
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
 hidden batch(es)


SELECT case_id, client_id, fva AS first_arrival, lvd AS last_departure FROM ( SELECT case_id, client_id, FIRST_VALUE(arrival) OVER (PARTITION BY case_id) AS fva, LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd FROM tmp_dba ORDER BY ID ) AS sq GROUP BY case_id, client_id, fva, lvd ORDER BY case_id, client_id
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
 hidden batch(es)


EXPLAIN ANALYZE SELECT case_id, client_id, -- removed ID fields! MIN(arrival) AS arrival, IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure -- non-standard! FROM tmp_dba GROUP BY case_id, client_id ORDER BY case_id;
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ANALYZE SELECT case_id, client_id, -- removed ID fields! MIN(arrival) ...' at line 1
 hidden batch(es)


EXPLAIN ANALYZE -- same plans as for IF - why not use standard construct? SELECT case_id, client_id, MIN(arrival) AS m_d, CASE WHEN MAX(COALESCE(departure, '2038-01-01')) = '2038-01-01' THEN NULL ELSE MAX(COALESCE(departure, '2038-01-01')) END AS m_d FROM tmp_dba GROUP BY case_id, client_id ORDER BY case_id, client_id;
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ANALYZE -- same plans as for IF - why not use standard construct? SELECT c...' at line 1
 hidden batch(es)


EXPLAIN ANALYZE SELECT case_id, client_id, fva AS first_arrival, lvd AS last_departure FROM ( SELECT case_id, client_id, FIRST_VALUE(arrival) OVER (PARTITION BY case_id) AS fva, LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd FROM tmp_dba ORDER BY ID ) AS sq GROUP BY case_id, client_id, fva, lvd ORDER BY case_id, client_id
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ANALYZE SELECT case_id, client_id, fva AS first_arrival, lvd AS last_depar...' at line 1
 hidden batch(es)


EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT case_id, client_id, MIN(arrival) AS m_d, CASE WHEN MAX(COALESCE(departure, '2038-01-01')) = '2038-01-01' THEN NULL ELSE MAX(COALESCE(departure, '2038-01-01')) END AS m_d FROM tmp_dba GROUP BY case_id, client_id ORDER BY case_id, client_id;
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ANALYZE, BUFFERS, VERBOSE) SELECT case_id, client_id, MIN(arrival) AS m_d...' at line 1
 hidden batch(es)


EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT case_id, client_id, fva AS first_arrival, lvd AS last_departure FROM ( SELECT case_id, client_id, FIRST_VALUE(arrival) OVER (PARTITION BY case_id) AS fva, LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd FROM tmp_dba ORDER BY ID ) AS sq GROUP BY case_id, client_id, fva, lvd ORDER BY case_id, client_id
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ANALYZE, BUFFERS, VERBOSE) SELECT case_id, client_id, fva AS first_arrival...' at line 1
 hidden batch(es)