[ACCEPTED]-Select most recent row with GROUP BY in MySQL-group-by

You want the groupwise maximum; in essence, group the payments 7 table to identify the maximal records, then 6 join the result back with itself to fetch 5 the other columns:

SELECT users.*, payments.method, payments.id AS payment_id
FROM   payments NATURAL JOIN (
  SELECT   user_id, MAX(id) AS id 
  FROM     payments
  GROUP BY user_id
) t RIGHT JOIN users ON users.id = t.user_id

Note that MAX(id) may not be 4 the "most recent payment", depending on your application 3 and schema: it's usually better to determine 2 "most recent" based off TIMESTAMP than based off synthetic 1 identifiers such as an AUTO_INCREMENT primary key column.

I read the following solution on SO long 16 ago, but I can't find the link to credit, but 15 here goes:

SELECT users.*, payments.method, payments.id AS payment_id, payments2.id
FROM users
JOIN payments
    ON users.id = payments.user_id 
LEFT JOIN payments2
    ON payments.user_id = payments2.user_id
    AND payments.id < payments2.id
WHERE payments2.id IS NULL

To understand how this works, just 14 drop the WHERE payments2.id IS NULL and you'll see what is happening, for 13 instance it could produce the following 12 output (I haven't build the schema to test 11 this, so it's pseudo-output). Assume there 10 are the following records in payments:

id | user_id | method
1  | 1       | VISA
2  | 1       | VISA
3  | 1       | VISA
4  | 1       | VISA

And the above 9 SQL (without the WHERE payments2.id IS NULL clause) should produce:

users.id | payments.method | payments.id | payments2.id
1        | VISA            | 1           | 2
1        | VISA            | 1           | 3
1        | VISA            | 1           | 4
1        | VISA            | 2           | 3
1        | VISA            | 2           | 4
1        | VISA            | 3           | 4
1        | VISA            | 4           | NULL

As 8 you can see the the last line produces the 7 desired result, and since there's no payments2.id > 4, the 6 LEFT JOIN results in a payments2.id = NULL.

I've found this 5 solution to be much faster (from my early 4 tests) than the accepted answer.

Using a 3 different schema but a similar query, of 2 16095 records:

select as1.*, as2.id
from allocation_status as1
left join allocation_status as2 
    on as1.allocation_id = as2.allocation_id
    and as1.id < as2.id
where as2.id is null;

16095 rows affected, taking 4.1ms

Compared to the accepted answer 1 of MAX / subquery:

SELECT as1.* 
FROM allocation_status as1
JOIN (
    SELECT max(id) as id
    FROM allocation_status
    group by allocation_id
) as_max on as1.id = as_max.id 

16095 rows affected, taking 14.8ms

I've just been dealing with pretty much 7 exactly the same problem and found these 6 answers helpful. My testing seems to suggest 5 you can make it slightly simpler than the 4 accepted answer, viz.:

SELECT u.*, p.method, p.id AS payment_id 
FROM `users` u, `payments` p
WHERE u.id = p.user_id 
    AND p.id = (SELECT MAX(p2.id) FROM payments p2
                    WHERE p2.user_id = u.id);

I've not performance 3 tested the differences but the db I'm working 2 on has over 50,000 Users and over 60,000 1 payments and the query runs in 0.024 seconds.

My solution:

SELECT

u.codigo, 
u.nome,  
max(r.latitude),  
max(r.longitude),  
max(r.data_criacao) 

from TAB_REGISTRO_COORDENADAS  r

inner join TAB_USUARIO u

on u.codigo = r.cd_usuario

group by u.codigo

0

Taking this one step further, we can also 4 use:

select payment_id, cust_id, amount, payment_method 
from my_table where payment_id in 
(
    select max(payment_id) from my_table group by cust_id
);

...but this query is also taking way 3 too long in my context. The inner select 2 is smoking fast, but the outer takes a while, and 1 with only 124 results from the inner. Ideas?