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. 2591511 fiddles created (45731 in the last week).

select version();
version()
8.0.22
 hidden batch(es)


CREATE TABLE student ( s_id INT NOT NULL PRIMARY KEY, s_name VARCHAR (100) NOT NULL UNIQUE, s_email VARCHAR (255) NOT NULL );
 hidden batch(es)


INSERT INTO student VALUES (1, 'name_1', 'email_1@domain_1.net'), (2, 'name_2', 'email_2@domain_2.net'), (3, 'name_3', 'email_3@domain_3.net'), (4, 'name_4', 'email_3@domain_3.net');
 hidden batch(es)


CREATE TABLE event ( e_id INT NOT NULL PRIMARY KEY, e_name VARCHAR (100) NOT NULL, e_date_scheduled DATE NOT NULL );
 hidden batch(es)


INSERT INTO event VALUES (1, 'event_1', '2020-01-01'), (2, 'event_2', '2020-02-01'), (3, 'event_3', '2020-03-01'), (4, 'event_4', '2020-04-01'), (5, 'event_5', '2020-05-01'), (6, 'event_6', '2020-06-01');
 hidden batch(es)


CREATE TABLE attendance ( -- a_id INT NOT NULL PRIMARY KEY, -- << NOT necessary! a_sid INT NOT NULL, a_eid INT NOT NULL, CONSTRAINT a_s_e_pk PRIMARY KEY (a_sid, a_eid), CONSTRAINT a_sid_fk FOREIGN KEY (a_sid) REFERENCES student(s_id), CONSTRAINT a_eid_fk FOREIGN KEY (a_eid) REFERENCES event(e_id) );
 hidden batch(es)


INSERT INTO attendance VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 2), (2, 3), (2, 5), (3, 5), (3, 6);
 hidden batch(es)


SELECT s.s_id, s.s_name, s.s_email, COUNT(a.a_sid) AS show_count FROM student s LEFT JOIN attendance a ON a.a_sid = s.s_id GROUP BY s.s_id, s.s_name, s.s_email ORDER BY s.s_id;
s_id s_name s_email show_count
1 name_1 email_1@domain_1.net 4
2 name_2 email_2@domain_2.net 3
3 name_3 email_3@domain_3.net 2
4 name_4 email_3@domain_3.net 0
 hidden batch(es)


SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam, ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn FROM student s2 LEFT JOIN attendance a2 ON s2.s_id = a2.a_sid LEFT JOIN event e ON a2.a_eid = e.e_id ORDER BY s2.s_id, e.e_date_scheduled DESC;
sid eid eds enam rn
1 4 2020-04-01 event_4 1
1 3 2020-03-01 event_3 2
1 2 2020-02-01 event_2 3
1 1 2020-01-01 event_1 4
2 5 2020-05-01 event_5 1
2 3 2020-03-01 event_3 2
2 2 2020-02-01 event_2 3
3 6 2020-06-01 event_6 1
3 5 2020-05-01 event_5 2
4 1
 hidden batch(es)


SELECT sid, eid, eds, enam FROM ( SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam, ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn FROM student s2 LEFT JOIN attendance a2 ON s2.s_id = a2.a_sid LEFT JOIN event e ON a2.a_eid = e.e_id ORDER BY s2.s_id, e.e_date_scheduled DESC ) AS t WHERE t.rn = 1;
sid eid eds enam
1 4 2020-04-01 event_4
2 5 2020-05-01 event_5
3 6 2020-06-01 event_6
4
 hidden batch(es)


SET PROFILING = 1;
 hidden batch(es)


SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail, COUNT(a.a_sid) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event" FROM student s LEFT JOIN attendance a ON s.s_id = a.a_sid JOIN ( SELECT sid, eid, eds, enam FROM ( SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam, ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn FROM student s2 LEFT JOIN attendance a2 ON s2.s_id = a2.a_sid LEFT JOIN event e ON a2.a_eid = e.e_id ORDER BY s2.s_id, e.e_date_scheduled DESC ) AS t WHERE t.rn = 1 ) AS t2 ON s.s_id = t2.sid GROUP BY s.s_id, s.s_name, s.s_email, t2.eds, t2.enam ORDER BY s.s_id, t2.eds -- << Can invert depending on requirements
sid snam smail a_cnt eds Latest event
1 name_1 email_1@domain_1.net 4 2020-04-01 event_4
2 name_2 email_2@domain_2.net 3 2020-05-01 event_5
3 name_3 email_3@domain_3.net 2 2020-06-01 event_6
4 name_4 email_3@domain_3.net 0 -----
 hidden batch(es)


SHOW PROFILES;
Query_ID Duration Query
1 0.00111600 SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail, COUNT(a.a_sid) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event" FROM student s LEFT JOIN attendance a ON s.s_id = a.a_sid JOIN ( SELECT sid, eid, eds, enam FROM ( SELECT s2.s_id AS sid, e
 hidden batch(es)


EXPLAIN ANALYZE SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail, COUNT(a.a_sid) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event" FROM student s LEFT JOIN attendance a ON s.s_id = a.a_sid JOIN ( SELECT sid, eid, eds, enam FROM ( SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam, ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn FROM student s2 LEFT JOIN attendance a2 ON s2.s_id = a2.a_sid LEFT JOIN event e ON a2.a_eid = e.e_id ORDER BY s2.s_id, e.e_date_scheduled DESC ) AS t WHERE t.rn = 1 ) AS t2 ON s.s_id = t2.sid GROUP BY s.s_id, s.s_name, s.s_email, t2.eds, t2.enam ORDER BY s.s_id, t2.eds -- << Can invert depending on requirements
EXPLAIN
-> Sort: s.s_id, t2.eds (actual time=0.277..0.278 rows=4 loops=1) -> Table scan on <temporary> (actual time=0.001..0.001 rows=4 loops=1) -> Aggregate using temporary table (actual time=0.265..0.266 rows=4 loops=1) -> Nested loop left join (actual time=0.161..0.198 rows=10 loops=1) -> Nested loop inner join (actual time=0.155..0.172 rows=4 loops=1) -> Filter: (t.rn = 1) (actual time=0.147..0.151 rows=4 loops=1) -> Table scan on t (cost=3.85 rows=12) (actual time=0.000..0.002 rows=10 loops=1) -> Materialize (actual time=0.146..0.149 rows=10 loops=1) -> Sort: s2.s_id, e.e_date_scheduled DESC (actual time=0.133..0.134 rows=10 loops=1) -> Table scan on <temporary> (actual time=0.004..0.005 rows=10 loops=1) -> Temporary table (actual time=0.123..0.125 rows=10 loops=1) -> Window aggregate: row_number() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id,e.e_id desc ) (actual time=0.093..0.101 rows=10 loops=1) -> Sort: s2.s_id, s2.s_id, e.e_id DESC (actual time=0.090..0.092 rows=10 loops=1) -> Stream results (cost=7.05 rows=12) (actual time=0.027..0.077 rows=10 loops=1) -> Nested loop left join (cost=7.05 rows=12) (actual time=0.024..0.068 rows=10 loops=1) -> Nested loop left join (cost=2.85 rows=12) (actual time=0.019..0.040 rows=10 loops=1) -> Index scan on s2 using s_name (cost=0.65 rows=4) (actual time=0.011..0.014 rows=4 loops=1) -> Index lookup on a2 using PRIMARY (a_sid=s2.s_id) (cost=0.33 rows=3) (actual time=0.004..0.006 rows=2 loops=4) -> Single-row index lookup on e using PRIMARY (e_id=a2.a_eid) (cost=0.26 rows=1) (actual time=0.002..0.003 rows=1 loops=10) -> Single-row index lookup on s using PRIMARY (s_id=t.sid) (cost=0.33 rows=1) (actual time=0.005..0.005 rows=1 loops=4) -> Index lookup on a using PRIMARY (a_sid=t.sid) (cost=0.50 rows=3) (actual time=0.003..0.005 rows=2 loops=4)
 hidden batch(es)