CODEWITHSUNDEEP
mysqlsql
Devil's Dream
Devil's Dream

Reputation: 715

Query of getting distinct latest value from table

In MySQL I am trying to get the only one latest data of each user. I am cureently doing the following query

SELECT DISTINCT username, value, date
FROM table
WHERE date >=  CURDATE()
ORDER BY date DESC

Its returning all the values by username and the query is to slow. Is there any way to make it faster and get only one latest value of each user?

Upvotes: 0

Views: 47

Answers (2)

jarlh
jarlh

Reputation: 44696

Do a GROUP BY with MAX() in a sub-query to find each user's last date. Join with that result:

SELECT t1.username, t1.value, t1.date
FROM table t1
JOIN (select username, max(date) as maxdate from table
      group by username) t2 on t1.username = t2.username
                           and t1.date = t2.maxdate
WHERE t1.date >= CURDATE()
ORDER BY t1.date DESC

Upvotes: 1

mohan111
mohan111

Reputation: 8865

mostly if you are looking for latest date of each username. Hope this query helps 

SELECT  T.username, T.value, TT.date
FROM table T
WHERE T.Date = (SELECT MAX(TT.Date)
                 FROM table TT
                 WHERE TT.user = T.user)

Upvotes: 1

Related Questions