getting all records with latest date [ERROR]

Question

I have this table that I want to return the firstname and fathername of all the records with the latest data ,should be dynamic whenever a new latest date is inserted to return that record. I've tried this

SELECT rh.firstname, rh.fathername, rh.bloodonation_date
FROM personprofile rh,
  (SELECT MAX(bloodonationdate) AS maxdate, firstname
   FROM report_history
   GROUP BY firstname) maxresults
WHERE rh.firstname= maxresults.firstname
AND rh.bloodonation_date= maxresults.bloodonation_date;

So if I have this table

firstname, fathername,bloodonation_date
--------------------------
Ahmed     issa   2018-12-24
Noora     issa   2018-12-21
Joseph    John   2018-12-24
Ash       Scott  2018-12-24
Isacf     jaack  2018-12-24
Ashley    Make   2018-12-24
James     Ma     2018-12-20
cd        cd     2018-12-24
cde       cde    2018-12-24
Marc      Mac    2018-12-16
Noora     Ahmed  2018-12-15
Rabeew    fbdb   2018-11-15

How do I get it to return

firstname, fathername,bloodonation_date
--------------------------
Ahmed     issa   2018-12-24
Joseph    John   2018-12-24
Ash       Scott  2018-12-24
Isacf     jaack  2018-12-24
Ashley    Make   2018-12-24
cd        cd     2018-12-24

Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 03 '18 at 06:49
Possible duplicate of [Get the latest date from grouped MySQL data](https://stackoverflow.com/questions/8470311/get-the-latest-date-from-grouped-mysql-data) — Nick, Nov 03 '18 at 06:51

score 1 · Answer 1 · answered Nov 03 '18 at 06:32

You can try to use correlate subquery.

Schema (MySQL v5.7)

CREATE TABLE T(
   firstname VARCHAR(50),
    fathername VARCHAR(50),
bloodonation_date DATE
);



INSERT INTO T VALUES ('Ahmed','issa','2018-12-24');
INSERT INTO T VALUES ('Noora','issa','2018-12-21');
INSERT INTO T VALUES ('Joseph','John','2018-12-24');
INSERT INTO T VALUES ('Ash','Scott','2018-12-24');
INSERT INTO T VALUES ('Isacf','jaack','2018-12-24');
INSERT INTO T VALUES ('Ashley','Make','2018-12-24');
INSERT INTO T VALUES ('James','Ma','2018-12-20');
INSERT INTO T VALUES ('cd','cd','2018-12-24');
INSERT INTO T VALUES ('cde','cde','2018-12-24');
INSERT INTO T VALUES ('Marc','Mac','2018-12-16');
INSERT INTO T VALUES ('Noora','Ahmed','2018-12-15');
INSERT INTO T VALUES ('Rabeew','fbdb','2018-11-15');

Query #1

SELECT *
FROM T 
WHERE bloodonation_date = (
  SELECT  MAX(bloodonation_date) 
  FROM T                       
);

| firstname | fathername | bloodonation_date |
| --------- | ---------- | ----------------- |
| Ahmed     | issa       | 2018-12-24        |
| Joseph    | John       | 2018-12-24        |
| Ash       | Scott      | 2018-12-24        |
| Isacf     | jaack      | 2018-12-24        |
| Ashley    | Make       | 2018-12-24        |
| cd        | cd         | 2018-12-24        |
| cde       | cde        | 2018-12-24        |

View on DB Fiddle

just did that also. would like to add, that I found that DB fiddle site to be useful and will bookmark it for future use. — CecilMerrell aka bringrainfire, Nov 03 '18 at 07:26

score 0 · Accepted Answer · answered Nov 05 '18 at 05:51

0

You can use a sub query here:

select * from personprofile where bloodonation_date=(select max(bloodonation_date) from personprofile) order by 1 , Check solution here

answered Nov 05 '18 at 05:51

Brekhnaa

36
4

getting all records with latest date [ERROR]

2 Answers2