CODEWITHSUNDEEP
phpmysqlsql
user4041472
user4041472

Reputation:

Get last inserted data of multiple IDs from a table?

I have a table "event_activities" and it records the activities of all events.

It has a field 'event_id' that is 'id' in "events" table. Now I want to retrieve last inserted row of each event and event should be distinct. 

event_activities
------------------
id eventid date


events
----------
id name place

I have tried using DISTINCT but I also need some more fields and I also tried using group by but was unsuccessful.

select distinct a.id, a.eventid, a.date, b.name from event_activities as a join events as b on a.eventid = b.id;

and 

select a.id, a.eventid, a.date, b.name from event_activities as a join events as b on a.eventid = b.id order by a.eventid;

Both queries does not gave me required result. Can someone bring a query that do my job.

Upvotes: 2

Views: 146

Answers (1)

Mikhail Timofeev
Mikhail Timofeev

Reputation: 2169

select a.id, a.eventid, a.date, b.name 
from event_activities as a join events as b on a.eventid = b.id 
where a.date = (select max(date) from event_activities a2 
                where a2.eventid = a.eventid)
order by a.eventid

Upvotes: 2

Related Questions