Im having a brain fart as to how I would do this. I need to select only the latest entry in a group of same id entries
I have records in an appointment table.
lead_id app_id
4 42
3 43
1 44
2 45
2 46 (want this one)
1 48
3 49 (this one)
4 50 (this one)
1 51 (this one)
The results I require are app_id 46,49,50,51
Only the latest entries in the appointment table, based on duplicate lead_id identifiers.
I think this is much more optimal and efficient way of doing it (sorting next grouping):
SELECT * FROM (
SELECT * FROM appointment
ORDER BY lead_id, app_id DESC
) AS ord
GROUP BY lead_id
this will be useful when you need all other fields too from the table without complicated queries
Result:
lead_id app_id
1 51
2 46
3 49
4 50
Here is the query you're looking for:
SELECT A.lead_id
,MAX(A.app_id) AS [last_app_id]
FROM appointment A
GROUP BY A.lead_id
If you want to have every columns corresponding to these expected rows:
SELECT A.*
FROM appointment A
INNER JOIN (SELECT A2.lead_id
,MAX(A2.app_id) AS [last_app_id]
FROM appointment A2
GROUP BY A2.lead_id) M ON M.lead_id = A.lead_id
AND M.last_app_id = A.app_id
ORDER BY A.lead_id
Here i simply use the previous query for a jointure in order to get only the desired rows.
Hope this will help you.
The accepted answer by George Garchagudashvili is not a good answer, because it has group by with unaggregated columns in the select. Select * with group by is simply something that should not be allowed in SQL -- and it isn't in almost all databases. Happily, the default version of the more recent versions of MySQL also rejects this syntax.
An efficient solution is:
select a.*
from appointment a
where a.app_id = (select max(a2.app_id)
from appointment a2
where a2.lead_id = a.lead_id
);
With an index on appointment(lead_id, app_id), this should be as fast or faster than George's query.