SQL query that throws away rows that are older & satisfy a condition ?

Question

Issuing the following query:

  SELECT t.seq, 
         t.buddyId, 
         t.mode, 
         t.type, 
         t.dtCreated 
    FROM MIM t 
   WHERE t.userId = 'ali' 
ORDER BY t.dtCreated DESC;

...returns me 6 rows.

+-------------+------------------------+------+------+---------------------+
|         seq | buddyId                | mode | type | dtCreated           |
+-------------+------------------------+------+------+---------------------+
|          12 | [email protected] |    2 |    1 | 2009-09-14 12:39:05 |
|          11 | [email protected] |    4 |    1 | 2009-09-14 12:39:02 |
|          10 | [email protected]  |    1 |   -1 | 2009-09-14 12:39:00 |
|           9 | [email protected] |    1 |   -1 | 2009-09-14 12:38:59 |
|           8 | [email protected]  |    2 |    1 | 2009-09-14 12:37:53 |
|           7 | [email protected] |    2 |    1 | 2009-09-14 12:37:46 |
+-------------+------------------------+------+------+---------------------+

I want to return rows based on this condition:

1. If there are duplicate rows with the same buddyId, only return me the latest (as specified by dtCreated).

So, the query should return me:

+-------------+------------------------+------+------+---------------------+
|         seq | buddyId                | mode | type | dtCreated           |
+-------------+------------------------+------+------+---------------------+
|          12 | [email protected] |    2 |    1 | 2009-09-14 12:39:05 |
|          10 | [email protected]  |    1 |   -1 | 2009-09-14 12:39:00 |
+-------------+------------------------+------+------+---------------------+

I've tried with no success to use a UNIQUE function but it's not working. Thanks.

Answer 1

This should only return the most recent entry for each userId.

SELECT a.seq
     , a.buddyId
     , a.mode
     , a.type
     , a.dtCreated
FROM mim AS [a]
JOIN (SELECT MAX(dtCreated) FROM min GROUP BY buddyId) AS [b]
     ON a.dtCreated = b.dtCreated
     AND a.userId = b.userId
WHERE userId='ali'
ORDER BY dtCreated DESC;