self join plus limit unique

[sasori]

hi, i was given this situation

 

SQL Test: Imagine you have a User table for a twitter-like web application that looks like this: Username, Status Update, Date Updated          (eg. Ann123, "Hi, I am at Startbucks!", 01/26/12 10:00:01). Write a query that selects only the latest status update for each user.

 

and so I created my own dummy table to test what I thought would be the answer

 

CREATE TABLE USER( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
`status` VARCHAR(50),
`dateupdated` DATETIME
) ENGINE = INNODB;  

 

then I ended up with this query

 

SELECT user.`username`, latest.status AS latest_status , user.`dateupdated` FROM USER LEFT JOIN USER AS latest ON user.`id` = latest.id ORDER BY dateupdated 

 

but it seem like it's still wrong, since I used an ID as primary key, the username can be repeated  and the question scenario should only give the unique latest update for each user, "I think" ...any suggestions how to fix this problem ?

 

[gizmola]

First of all you do not want a LEFT JOIN.  A LEFT JOIN is really shorthand for an "outer" join, where you will get a row for every row in the "LEFT" table.  In this case the left table is going to be the table named in the FROM clause.  What you actually want is an inner join, which in terms of shorthand, is simply a "JOIN".

 

It's always useful in these problems to break things down into individual queries.  Start with how you would get the most recent "dateupdated' for each username?

 

SELECT username, MAX(dateupdated) as dateUpdated
FROM USER
GROUP BY username

 

So it's clear in this case, that to pull a single row out, you need to match both the username and the MAX(dateupdated) result from the GROUP BY.  You can do this by aliasing the query and then joining to it.

 

SELECT id, u.username, status, u.dateupdated 
FROM USER u 
JOIN 
(SELECT username, MAX(dateupdated) as dateupdated FROM USER GROUP BY username)  ul 
ON u.username = ul.username AND u.dateupdated = ul.dateupdated; 

 

 

 

 

[sasori]

this is the important thing i learned from ya

 

It's always useful in these problems to break things down into individual queries.

 

thanks for the reminder