This is yet another installment of the MySQL sql_mode = only_full_group_by
saga.
<rant>
The problem is that MySQL's default (till 8) will return results which, depending on the order of the records being INSERT
ed into the table (and/or PRIMARY KEY
ordering), will vary and be correct or wrong DEPENDING. This is absolutely the worst sort of difficult to reproduce error that is so typical of MySQL.
<\rant>
However, it is good to see that MySQL's default behaviour is now the correct one (more than 25 years after PostgreSQL, FirebirdSQL, and the major vendors (Oracle, SQL Server &c...)!!).
What you need to do is the following:
-- create the tables - the fiddle (for MySQL version 8 is here)
CREATE TABLE memb
(
last_m VARCHAR (30),
CONSTRAINT memb_pk PRIMARY KEY (last_m)
);
-- and
CREATE TABLE tblprint
(
last_m VARCHAR (30),
printdate DATETIME NOT NULL,
CONSTRAINT tp_pk PRIMARY KEY (last_m, printdate),
CONSTRAINT tp_memb_fk FOREIGN KEY (last_m) REFERENCES memb (last_m)
);
-- see end of this answer for populating these tables with data
I added more records and deliberately inserted them in an order which was not that of the dates either ASCENDING
or DESCENDING
- see discussion below.
The correct answer is
SELECT
m.last_m,
MAX(tp.printdate) AS "p_date"
-- COALESCE(MAX(tp.printdate), '"NULL"') AS "for_display" -- optional, just for show!
FROM
tblprint tp
RIGHT JOIN memb m
ON tp.last_m = m.last_m
GROUP BY last_m
ORDER BY last_m, p_date;
Strictly speaking, the RIGHT JOIN
should be a RIGHT OUTER JOIN
.
Result:
last_m p_date
ANDRES, JOSE 2024-07-29 13:33:24
LORISA, MARIA 2021-07-29 13:37:44
SENINA, JUSTINA
TAN, LOLITA
which is the correct result, no matter what the record insertion order is.
Now, there is an alternative fiddle for version 5.6 (available here) which is the closest to 5.5 which you are using. A dbfiddle.com version for 5.5 is available here - it appears to take the first date arbitrarily by PRIMARY KEY
order - so it selects the smallest.
As you will be able to see - the defauts of MySQL, the 5.6 version, are much more permissive than 8. You can see that sql_mode = only_full_group_by
for version 8. You can set this (HIGHLY RECOMMENDED) for version 5.5. I would also strongly recommend that you upgrade to version 8 - it's been around a while now.
You will also find that if you experiment with the record insertion order, you can get different results in 5.6 - for example, if by coincidence, the data is inserted in reverse date order by last_m into the tblprinter
table, you will be able to get the "correct" results from @HiteshRathi 's query - of course this is very very dangerous!
I recommend that you "play around" with the various queries in the fiddles and see what happens - record insertion order and PRIMARY KEY
order are factors to look at. Make sure your queries don't return different results with differing record insertion orders and/or PRIMARY KEY
order.
-------------------- Data for the tables ---------------------------
INSERT INTO memb VALUES ('ANDRES, JOSE');
INSERT INTO memb VALUES ('LORISA, MARIA');
INSERT INTO memb VALUES ('SENINA, JUSTINA');
INSERT INTO memb VALUES ('TAN, LOLITA');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2019-07-29 13:39:44');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2001-09-01 23:33:41');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2002-07-29 13:33:42');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2003-07-29 13:33:43');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2024-07-29 13:33:24');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2020-07-29 20:20:20');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2014-07-29 13:34:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2007-07-29 13:31:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2008-07-29 13:32:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2009-07-29 13:33:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2018-07-29 13:35:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2021-07-29 13:37:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2020-07-29 13:36:44');