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. 2591595 fiddles created (45745 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, a_sid INT NOT NULL, CONSTRAINT a_sid_fk FOREIGN KEY (a_sid) REFERENCES student(s_id), a_eid INT NOT NULL, CONSTRAINT a_eid_fk FOREIGN KEY (a_eid) REFERENCES event(e_id) );
 hidden batch(es)


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


SELECT s.s_id, s.s_name, s.s_email, COUNT(a.a_id) 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_id) 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.00159975 SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail, COUNT(a.a_id) 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_id) 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.295..0.296 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.284..0.285 rows=4 loops=1) -> Nested loop left join (actual time=0.202..0.235 rows=10 loops=1) -> Nested loop inner join (actual time=0.195..0.212 rows=4 loops=1) -> Filter: (t.rn = 1) (actual time=0.187..0.192 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.186..0.189 rows=10 loops=1) -> Sort: s2.s_id, e.e_date_scheduled DESC (actual time=0.173..0.174 rows=10 loops=1) -> Table scan on <temporary> (actual time=0.001..0.002 rows=10 loops=1) -> Temporary table (actual time=0.162..0.164 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.145..0.154 rows=10 loops=1) -> Sort: s2.s_id, s2.s_id, e.e_id DESC (actual time=0.141..0.144 rows=10 loops=1) -> Stream results (cost=8.05 rows=12) (actual time=0.037..0.125 rows=10 loops=1) -> Nested loop left join (cost=8.05 rows=12) (actual time=0.035..0.114 rows=10 loops=1) -> Nested loop left join (cost=3.85 rows=12) (actual time=0.029..0.081 rows=10 loops=1) -> Index scan on s2 using s_name (cost=0.65 rows=4) (actual time=0.013..0.015 rows=4 loops=1) -> Index lookup on a2 using a_sid_fk (a_sid=s2.s_id) (cost=0.57 rows=3) (actual time=0.014..0.016 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.003..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.004..0.004 rows=1 loops=4) -> Index lookup on a using a_sid_fk (a_sid=t.sid) (cost=0.50 rows=3) (actual time=0.003..0.005 rows=2 loops=4)
 hidden batch(es)