SQL to select Unique list of user, along with other data depending upon timestamp

Question

I have a table in SQL, The table is like this:

+-------------+---------------+-------------------------+
| quotes_user | email_address | ________Sent_at________ |
+-------------+---------------+-------------------------+
| user1       | email1        | 2012-10-09 12:23:53.253 |
| user1       | email2        | 2012-10-09 12:24:53.253 |
| user2       | email3        | 2012-10-09 13:20:53.253 |
| user2       | email4        | 2012-10-09 11:23:53.253 |
| user3       | email5        | 2012-10-08 10:29:53.253 |
| user3       | email6        | 2012-10-08 14:23:53.253 |
+-------------+---------------+-------------------------+

I want the result to show

+-------------+---------------+-------------------------+
| quotes_user | email_address | ________Sent_at________ |
+-------------+---------------+-------------------------+
| user1       | email2        | 2012-10-09 12:24:53.253 |
| user2       | email3        | 2012-10-09 13:20:53.253 |
| user3       | email6        | 2012-10-08 14:23:53.253 |
+-------------+---------------+-------------------------+

i.e. I want to select List of unique users, and the latest email address associated with them.

Another way to explain the problem would be, that I want to select a field which is not included in any aggregate function or order by clause. I've tried many statements with lots of permutations of Distinct and Order By, Group By etc. to no use.

I'm trying to avoid multiple statements.

Please help me out with this.

Answer 1

The idea behind the query is to get their maximum Sent_AT for each quotes_user inside a subquery and join it back to the original table.

SELECT  a.*
FROM    tableName a INNER JOIN
        (
            SELECT  quotes_user, MAX(Sent_AT) maxSENT
            FROM tableName
            Group By quotes_user
        ) b on a.quotes_user = b.quotes_user AND
                a.Sent_AT = b.maxSent

SQLFiddle Demo

Answer 2

Try this:

SELECT t2.quotes_user, t2.email_address, t2.Sent_at AS '________Sent_at________'
FROM
(
   SELECT quotes_user, MAX(Sent_at) AS MaxDate
   FROM Table 
   GROUP BY quotes_user
) t1
INNER JOIN Table t2 ON  t1.quotes_user = t2.quotes_user 
                    AND t1.Sent_at = t2.MaxDate