Hello I have the following table design
ID account_id score date ------------------------------------------ 1 500 4 x 2 764 4 x 3 500 6 x 4 500 7 x 5 764 5 x
I'm trying to get all rows with the latest account_id entry
so my code should return
ID account_id score date ------------------------------------------ 4 500 7 x 5 764 5 x
I tried the following code but it seems to return the first entry but with the latest date
SELECT account_id,score, max(date) from table group by account_id
try it-
SELECT distinct a.id,a.account_id,b.score,b.date
FROM mytable b
JOIN
(
SELECT account_id,MAX(id) AS id
FROM mytable
GROUP BY account_id
) a ON a.account_id=b.account_id
ORDER BY a.id;
Case 1: If id is auto-increment column or max(id) means latest row.
select * from
(select * from table_name
order by id desc) temp
group by account_id
Case 2: If date column decides latest row then replace id by date in order clause and group clause.
This question is just a duplicate of SQL Select only rows with Max Value on a Column
There you'll find a good explanation.
SELECT a.*
FROM table a
INNER JOIN (
SELECT `account_id`,MAX(`date`) AS latest FROM table GROUP BY account_id
) b
ON a.`date` = b.`latest` AND a.`account_id` = b.`account_id`
ORDER BY `date` DESC
Reference :
Try using this query it works fine
Select a.account_id,a.score,a.date
from authors as a
join
(Select account_id,max(date) as date
from authors
group by account_id) as d
on(d.date=a.date)
