How to display the dates without DUPLICATE and ORDER by MAX DATE

Question

I just want to ask that how can I display the null values along with the MAX date.

SELECT 
  MEMB.MEMB_N,
  MEMB.LAST_M,
  PrintDate
FROM
  MEMB 
  INNER JOIN tblPrint 
    ON MEMB.MEMB_N = tblPrint.MEMB_N 
ORDER BY tblPrint.`PrintDate` DESC

My desired output will look like this but without duplicates.

Answer 1

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 INSERTed 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');

Answer 2

SELECT distinct MEMB.LAST_M LAST_M ,PrintDate as PrintDate 
FROM MEMB  
LEFT JOIN  tblPrint ON MEMB.MEMB_N = tblPrint.MEMB_N  
group by    
        MEMB.LAST_M ORDER BY PrintDate DESC;