Geek Answers Handbook

SQL Select to have the value appear only once.

For those who helped me along the way with this twitter clone, thanks! With your help, I managed to get the majority to work, and finally move on to the last steps of the followers function.

Now I have a data set with the following fields: username, tweet, date

Sample data might look like this:

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 struggled with an SQL statement that would create a dataset in which each user appears only once with his last tweet. So, based on the above, something similar to 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 searched for search queries on Google searches and tried the options COUNT, DISTINCT, MAX, but to no avail. Any help would be greatly appreciated! Thank!

+5
3
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)
+6

select distinct username, tweet, date order by date desc

( MSSQL)

:

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
+3
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
+1
source

More articles:


All Articles