Selecting latest entries for distinct entry

Question

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.

score 1 · Answer 1 · answered Sep 24 '14 at 20:49

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.

score 1 · Answer 2 · answered Mar 03 '18 at 23:10

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.

score -1 · Accepted Answer · answered Sep 24 '14 at 20:57

-1

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

answered Sep 24 '14 at 20:57

George G

7,443
12
45
59

thanks....cuz yes....I did need all the fields. I shoulda mentioned that in my post – Kylie Sep 24 '14 at 21:03
even if you use an ORDER BY in your subquery, the values returned will be indeterminate http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html – fthiella Sep 24 '14 at 21:09
Is this true? What do you mean by indeterminate? – Kylie Sep 24 '14 at 21:14
no he's not, he just don't understand how does sub-querying works :p – George G Sep 24 '14 at 21:15
@KyleK yes, it is a trick that is used often and it looks like it always work, but as the documentations says it's better not to rely on it. The correct answer is Joel's. – fthiella Sep 24 '14 at 21:16
@GeorgeGarchagudashvili it has been discussed often here on SO and it's a trick it's better not to use. Maybe it will work on current version of MySQL but things might change on future versions and you will get into trouble. Some other implementations of MySQL like MariaDB will already return wrong result. – fthiella Sep 24 '14 at 21:21
see for example here http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by – fthiella Sep 24 '14 at 21:26
1

So ther you would mention this too: `My question is a simple one then: Is there anyway to order rows before grouping without resorting to a subquery?` but you did not read question carefully, just trying to defense your wrong opinion with anything – George G Sep 24 '14 at 21:33
@GeorgeGarchagudashvili the author of the question is making your same mistake. But did you read the accepted answer with 60 upvotes? – fthiella Sep 24 '14 at 21:36
that problem is much different, there is field `post_date` which may have duplicate values (and this would not always return same result even for simple select query). so ordering with such columns may vary, will not go deeper for the case. but now we have not such a situation. in our case `app_id` is unique and there will not be two `51`s @fthiella – George G Sep 24 '14 at 21:51
1

@fthiella also he starts hes answer by: `Using an ORDER BY in a subquery is not the best solution to this problem.` so that means it depends on the problem, then I would say, any query may depend on the required task, and not always have guaranted results – George G Sep 24 '14 at 21:53
@GeorgeGarchagudashvili Is there a performance difference between your solution and the one with a `GROUP BY` in the subquery and the `ORDER BY` clause on the result? I never tried your approach because i've the impression of having less "control" on the data (it'll certainly works here but if i want the exact same kind of result on a different table where `app_id` is not unique, the result will probably not be the expected one...) – Joël Salamin Sep 25 '14 at 05:13
1

@JoëlSalamin your solution is the only one documented and certified, using ORDER BY/GROUP BY with non-aggregated column is considered bad practice and there are no guarantees that it will always work – fthiella Sep 25 '14 at 07:40
this got too messy, this is not fair, and shame on down voters. your minds can't for out of theories – George G Sep 25 '14 at 08:16

Selecting latest entries for distinct entry

3 Answers3

Linked