CODEWITHSUNDEEP
sqlsql-server
squareOne
squareOne

Reputation: 139

Get unique row by single column where duplicates exist

I have the following DB table called messages with columns:

thread_id, sender_id, receiver_id, date

Example:

+-----------+------------+-------------+-------------+
| thread_id | sender_id  | receiver_id |  date       |
+----------------------+-----------+-----------------+
|   1       | 1          |  2          | 11/06/2015  |
|   1       | 2          |  1          | 14/06/2015  |
|   1       | 1          |  2          | 14/06/2015  |
|   1       | 2          |  1          | 20/06/2015  |
|   2       | 1          |  3          | 12/06/2015  |
|   3       | 4          |  2          | 19/06/2015  |
|   3       | 2          |  2          | 20/06/2015  |
+-----------+------------+-------------+-------------+

I need a query that will select unique thread_id by earliest date and where thread_id exists more than once.

So my desired result would be:

+-----------+--------------+
| thread_id |   date       |
+-----------|--------------+
|   1       | 11/06/2015   |
|   3       | 19/06/2015   |
+-----------+--------------+

Upvotes: 2

Views: 46

Answers (2)

unbesiegbar
unbesiegbar

Reputation: 461

SELECT 
    MIN(date),thread_id 
FROM 
    messages 
GROUP BY 
    thread_id 
HAVING 
    COUNT(thread_id) > 1

Upvotes: 6

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use ROW_NUMBER() and COUNT()

;WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY thread_id ORDER BY [date]),
        cc = COUNT(*) OVER(PARTITION BY thread_id)
    FROM messages
)
SELECT
    thead_id, [date]
FROM Cte
WHERE
    rn = 1
    AND cc > 1

Upvotes: 3

Related Questions