Im trying to obtain a table with most recent exam date for unique name with their subject and phone number using MySqli

Question

This is My Main Table -

   field    acno    value   date
    a   11  12  17-11-2015
    b   11  3   17-11-2015
    c   11  234 17-11-2015
    d   11  2321    17-11-2015
    d   11  12  01-09-2016
    d   11  32  28-04-2016
    a   11  23  17-11-2015
    a   11  324 01-09-2016
    d   11  43  17-11-2015

   Output - I'm looking for

   field    acno    value   date
   a    11  324 01-09-2016
   b    11  3   17-11-2015
   c    11  234 17-11-2015
   d    11  12  01-09-2016

Name Subject Phone Number Exam_date Riya Maths 21321 10-10-2017 Riya bio 1223 09-01-2015 Riya english 123 09-09-2014 Riya math 4324 11-06-2014 Roy math 34241 11-11-2013 rubby bio&math 123123 12-08-2015 rubby english 123 05-01-2017 shabby bio 1235 11-04-2015 piya bio&math 243 12-10-2015 — Ajay Varma, Apr 18 '17 at 14:34
Please read [how to ask questions on StackOverflow](http://stackoverflow.com/help/how-to-ask) — Jay Blanchard, Apr 18 '17 at 14:35
Need to obtain a table which contains unique name with most recent exam date and also their corresponding phone and subject — Ajay Varma, Apr 18 '17 at 14:45

xQbert · Accepted Answer · 2017-04-19T14:06:20.650

0

Break the question down into parts:

First get the max exam date per name.

SELECT max(exam_date) max_exam_date, `field`, acno
FROM Table
GROUP BY field, acno

Then use the subset to limit the results on the entire dataset.

SELECT A.Name, A.Subject, A.Phone_number, A.Exam_date
FROM TABLE A
INNER JOIN (SELECT max(exam_date) max_exam_date, `field`, acno
            FROM Table
            GROUP BY `field`,  acno) B
 on A.`field` = b.`field`
and A.Acno = b.acno
and A. Exam_date = max_exam_date

This would be easier with an outer apply join, but I do not believe mySQL supports those at this time.

This does assume that "Name" is unique meaning if Bob is listed 4 times they are all the same "name"

Also note the phone number returned will be the one corresponding to the one with the max exam date. If two exams occur on the same max date, both will be returned.

edited Apr 19 '17 at 14:06

answered Apr 18 '17 at 14:40

xQbert

34,733
2
41
62

Ok. Let me check this. – Ajay Varma Apr 18 '17 at 14:42
In the future be sure to include the question, what you've tried and expected results ! – xQbert Apr 18 '17 at 14:43
Sure I will do it – Ajay Varma Apr 18 '17 at 14:48
Looks pretty easy to me! – Strawberry Apr 18 '17 at 14:51
@strawberry yeah It is pretty easy but the outer apply would negate the need for the inline view. makes it easier on the eyes, but not the head. Not enough people understand the cross/outer apply yet. (i'm one of them) – xQbert Apr 18 '17 at 14:52
It's a new one on me. Is it faster than the above? – Strawberry Apr 18 '17 at 15:07
From the reading I've done they are faster as the engine is able to better optimize the query resulting in more efficient execution plans; but I don't think you get the gain in ALL cases. http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join goes into some detail. I believe the performance gain to be especially true when you need the top or top N related records for a given object and their related records. – xQbert Apr 18 '17 at 15:34
@xQbert I tired the query which you provide but it seems something is wrong I will update the table once again – Ajay Varma Apr 19 '17 at 05:14
Join willc need to include acct no and field. I can't update at the moment will in a few hours and inner query will need those in select and group by. Change the fields and yes my query will not work. – xQbert Apr 19 '17 at 13:03
Ok updated. but when you say something is "wrong" i need to know the error your getting. or mock up your data/tables in a [SQLFiddle](http://sqlfiddle.com) and provide a link and I can work with it till we get the expected results. – xQbert Apr 19 '17 at 14:07

Im trying to obtain a table with most recent exam date for unique name with their subject and phone number using MySqli

1 Answers1