CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
kurisukun
kurisukun

Reputation: 3159

SQL Select to make a value appear only once

For those that have been helping me along the way with this twitter-clone, thank you!! With your help, I've managed to get most things working and finally now to the last steps of the followers function.

Right now, I have a dataset with the following fields: username, tweet, date

An example of the data could look like:

Username    Tweet             Date
kenny       hi!               2011-10-07 19:07:00
stan        hello             2011-10-05 18:07:00
kenny       looks like rain   2011-10-05 17:07:00
stan        hello             2011-10-05 14:07:00
cartman     authoritay!       2010-10-05 14:07:00

And I've been wrestling with the SQL statement that would produce a data set in which each user appears only once with their latest tweet. So, based on the above, something that looks like this:

Username    Tweet             Date
kenny       hi!               2011-10-07 19:07:00
stan        hello             2011-10-05 18:07:00
cartman     authoritay!       2010-10-05 14:07:00

I've been googling sql searches and have tried variations of COUNT, DISTINCT, MAX, but to no avail. Any help would be greatly appreciated! Thank you!

Upvotes: 10

Views: 62869

Answers (3)

Shakti Singh
Shakti Singh

Reputation: 86396

SELECT f.*
FROM (
      SELECT Username, MAX(`Date`) as maxval
      FROM  table GROUP BY Username    
     ) AS x INNER JOIN table AS f
ON f.Username  = x.Username  AND f.`Date`= x.maxval

Upvotes: 1

Christian Wattengård
Christian Wattengård

Reputation: 5681

Would it not work just by

select distinct username, tweet, date order by date desc

(This is MSSQL syntax)

With new data in hand:

SELECT DISTINCT tweets.username, content, date from tweets 
WHERE user_id IN (
  SELECT users.id FROM users 
  INNER JOIN user_users ON users.id = user_users.followed_user_id 
WHERE user_users.user_id = 1) 
ORDER BY date desc

Upvotes: 9

JB Nizet
JB Nizet

Reputation: 691755

select d1.username, d1.tweet, d1.date from data d1 where d1.date = 
    (select max(d2.date) from data d2 where d1.username = d2.username)

Upvotes: 9

Related Questions