CODEWITHSUNDEEP
mysql
user387049
user387049

Reputation: 6857

MySQL query for latest record per user

I have a table that looks like:

id_primary, id_user, amount, date

If I have entries that look like:

1, 1, 5, 2012-01-05
2, 1, 8, 2012-01-15
3, 1, 17, 2012-03-30
4, 2, 3, 2012-01-18
5, 2, 9.5, 2012-01-31

I've tried multiple queries to get the latest result (chronologically) for each id_user, however I seem to get the first row available no matter what. For example what I would want from this example table would be:

3, 1, 17, 2012-03-30
5, 2, 9.5, 2012-01-31

My latest attempt was something like this:

SELECT a.* FROM table a GROUP BY a.id_user HAVING MAX(a.date)

How can I fix this? Do I need to use a nested query somehow?

Upvotes: 1

Views: 3682

Answers (3)

Taryn
Taryn

Reputation: 247610

You can use a subquery to get the MAX() date by user

select t1.id_primary,
  t1.id_user,
  t1.amount,
  t2.maxdate
from yourtable t1
inner join
(
  select max(date) maxdate, id_user
  from yourtable
  group by id_user
) t2
  on t1.id_user = t2.id_user
  and t1.date = t2.maxdate

See SQL Fiddle with demo

Upvotes: 5

ABA
ABA

Reputation: 465

This my statement for similar situation $sql = "SELECT INDICADOR_IDIOMA FROM INDICADOR where INDICADOR_PK=(SELECT max(INDICADOR_PK) FROM INDICADOR WHERE INDICADOR_USUARIO=$pkUser)";

Upvotes: 0

eggyal
eggyal

Reputation: 125835

I 100% agree with @bluefeet's answer (+1 btw), but in MySQL it can be abbreviated as follows:

SELECT yourtable.* FROM yourtable NATURAL JOIN (
  SELECT id_user, MAX(date) date FROM yourtable GROUP BY id_user
) t

See it on sqlfiddle.

For your reference, this is known as the groupwise maximum.

Upvotes: 2

Related Questions