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.

John Woo · Answer 1 · 2012-10-10T07:46:11.293

7

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

edited Oct 10 '12 at 07:46

answered Oct 10 '12 at 06:54

John Woo

258,903
69
498
492

any problems? what do you want to know more? – John Woo Dec 11 '13 at 13:22

score 1 · Accepted Answer · answered Oct 10 '12 at 06:54

1

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

answered Oct 10 '12 at 06:54

Mahmoud Gamal

78,257
17
139
164

1

Works like a charm. Although I do not know How the JOIN part works, could you guide me to a good resource? – Sunny R Gupta Oct 10 '12 at 07:16
1

@SunnyRGupta - Here are some resources: [wikipedia - SQL Joins](http://en.wikipedia.org/wiki/Join_%28SQL%29), [Coding horror A Visual Explanation of SQL Joins-](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html), [Codeproject - Visual representation of SQL Join](http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins). Further more, I highly recommend [SQL Queries for Mere Mortals](http://rads.stackoverflow.com/amzn/click/0201433362) for the basics of SQL querying. – Mahmoud Gamal Oct 10 '12 at 07:21
1

Thanks a lot, the last link is extremely valuable :D – Sunny R Gupta Oct 10 '12 at 07:40
1

@SunnyRGupta - You're welcome any time. But start reading these, don't throw them away :) – Mahmoud Gamal Oct 10 '12 at 07:41

solaimuruganv · Answer 3 · 2012-10-10T07:27:29.183

0

 select quotes_user, email_address, sent_at 
 from table_name t1 inner join
   (select quote_user, max(sent_at) as sent
    from table_name group by quotes_uaser) t2  
 on t1.quotes_user = t2.quotes_user and t1.sent_at = t2.sent

edited Oct 10 '12 at 07:27

answered Oct 10 '12 at 06:55

solaimuruganv

27,177
1
18
23

1

The same answer as that by @Mahmoud. Please avoid duplicate answers. – Sunny R Gupta Oct 10 '12 at 07:44

score 0 · Answer 4 · answered Oct 10 '12 at 06:56

0

select quote_user, email_adress, max( ___sent_at___ ) from users group by quote_user

Supposing the biggest sent_at correponds with the last mail sent.

answered Oct 10 '12 at 06:56

Loïc bcn

154
5

1

Gives an error: Column 'email_adress' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Sunny R Gupta Oct 10 '12 at 07:03
Sorry, changing the group by clause for 'group by quote_user, email_adress' should work. – Loïc bcn Oct 10 '12 at 09:03
1

Works, but does not give the desired output. In fact this was the first code I tried myself. Did not work, so posted here after a few more tries. – Sunny R Gupta Oct 10 '12 at 10:17

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

4 Answers4

SQLFiddle Demo