CODEWITHSUNDEEP
mysql
Sadikhasan
Sadikhasan

Reputation: 18600

Get Latest Event from mysql

I have events table. I want latest event which event type='appointment' and group on type and instruction_id. Problem is event_date come first '2013-12-02' instead of '2013-12-05'. More information required give comment I explain in detail.

My Expected output :

ID  INSTRUCTION_ID  TYPE    COMMENT     EVENT_DATE
3   2   appointment     at home     December, 05 2013 00:00:00+0000
10  1   appointment     at home     November, 22 2013 00:00:00+0000
5   3   appointment     office  September, 17 2013 00:00:00+0000

For more information check SQL fiddle:

Upvotes: 0

Views: 436

Answers (2)

Alberto Garrido
Alberto Garrido

Reputation: 555

If you want the latest of only "appointment type" grouping by event type:

SELECT max(EVENT.event_date) -- use other fields
FROM EVENTS 
WHERE EVENTS.type='appointment'
GROUP BY EVENTS.instruction_id;

Because if you filter by one specific type you will only get the max date of that type. If you want to get the max date of each type

SELECT max(EVENT.event_date) -- use other fields
FROM EVENTS 
GROUP BY EVENTS.type, EVENTS.instruction_id

EDIT: if you add the rest of the fields is working as you expected. Anyway, I paste you the query tested:

    SELECT ID, INSTRUCTION_ID, TYPE, COMMENT, max(EVENT_DATE) EVENT_DATE 
    FROM EVENTS 
    WHERE EVENTS.type='appointment'
    GROUP BY EVENTS.instruction_id
ORDER BY EVENT_DATE;

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6854

try below query:

select * 
from 
(SELECT * 
FROM EVENTS 
WHERE EVENTS.type='appointment'
ORDER BY EVENTS.event_date DESC) EVENTS
GROUP BY EVENTS.type,EVENTS.instruction_id;

Query 2:

select * 
from 
(SELECT * 
FROM EVENTS 
WHERE EVENTS.type='appointment'
ORDER BY EVENTS.event_date DESC) EVENTS
GROUP BY EVENTS.type;

Upvotes: 1

Related Questions