Choosing latest record for group by

Asked Sep 28 '18 at 16:05

Active Sep 28 '18 at 16:12

Viewed 39 times

I have a query that pulls a list of records from a report and has a few other information tables join.

The table is a log of changes to the database and can contain several like, but not exactly alike records. We decided instead of displaying the long list of changes, we would only show the most recent change. I tried to use the group by function to show only the latest change per group, but i discovered that I can order or sort it to show only the most recent record. It seems to pick the first record in the list. The s reference table that stores names of other users who are not staff and who are being changed by the staff.

Here is my query

select *, old.methodName as oldName, new.methodName as newName, s.firstName as fName, s.lastName as lName from changeReport 
left join methodLookup as old on (old.methodID = changeReport.oldMethod) 
left join methodLookup as new on (new.methodID = changeReport.newMethod) 
join students on (s.ID = changeReport.ID) 
left join staffaccounts on (changeReport.staffID = staffaccounts.staffID) 
where newSubMethod = '$bnu' and date(timestamp) = CURRENT_DATE OR oldSubMethod = '$bnu' and date(timestamp) = CURRENT_DATE 
group by s.ID 
order by changeReport.rID desc

How do I modify this query (Maybe a sub query? I cant seem to get it to work) to show the data is already shows, but the most recent record in the group? Everything is time stamped properly.

edited Sep 28 '18 at 16:12

juergen d

201,996
37
293
362

asked Sep 28 '18 at 16:05

Robert Dickey

Could you provide some sample data and expect result? – D-Shih Sep 28 '18 at 16:08
1

one moment let me grab that – Robert Dickey Sep 28 '18 at 16:10
1

so you only want the last change from each group? – zebnat Sep 28 '18 at 16:13
Yes. Sorted by timestamp would be easy. I wanted to group by s.ID and then order them by timestamp. showing only the latest result. but currently its showing the first result it seems – Robert Dickey Sep 28 '18 at 16:16
@D-Shih there is too much data to post on here. Would take forever. I guess my only question is how can I make it so that group by only chooses the latest result? – Robert Dickey Sep 28 '18 at 16:19
@RobertDickey what is your MySQL version ?\ – Madhur Bhaiya Sep 28 '18 at 16:23
@MadhurBhaiya Server type: MariaDB Server version: 5.5.60-MariaDB - MariaDB Server Protocol version: 10 – Robert Dickey Sep 28 '18 at 16:24
Why was this marked duplicate? It isn't – Robert Dickey Sep 28 '18 at 17:34

Choosing latest record for group by

0 Answers0