Need help with SQL query?

Question

Here is my query. I need to select only one distinct record for each thread but because I'm returning Highlight its cannot be unique and returns two records for the same tread.

Please help me to modify my query to make it work. Thank you

SELECT DISTINCT T2.Name, T0.ThreadID, (CASE WHEN T0.Status = 0 THEN 1 ELSE 0 END) AS Highlight
FROM Messaging T0
INNER JOIN Table1 T1 ON T0.ThreadID = T1.ThreadID
INNER JOIN Table2 T2 ON T0.UserID = T2.UserID
INNER JOIN Table3 T3 ON T3.ID = T0.ID
WHERE  T0.UserID != 9149 
GROUP BY U.Name, M.ThreadID 
ORDER BY Highlight DESC

This query returns result like you can see below

Name    ThreadID                                Highlight
Alex    048C8E05-422C-491A-880C-AE370194AE8C        1
Andrew  048C8E05-422C-491A-880C-AE370194AE8B        0
Alex    048C8E05-422C-491A-880C-AE370194AE8C        0



Table T0

RecordID    UserID               ThreadID                                 Status
1           9090                 048C8E05-422C-491A-880C-AE370194AE8C     0
2           9149                 048C8E05-422C-491A-880C-AE370194AE8C     1
3           9090                 048C8E05-422C-491A-880C-AE370194AE8C     1
4           5454                 048C8E05-422C-491A-880C-AE370194AE8B     0
5           9149                 048C8E05-422C-491A-880C-AE370194AE8B     0

Answer 1

You have to pick which value to use for status/highlight if there's more than one. Here's one option - use max():

SELECT DISTINCT T2.Name, T0.ThreadID, max(CASE WHEN T0.Status = 0 THEN 1 ELSE 0 END) AS Highlight
FROM Messaging T0
INNER JOIN Table1 T1 ON T0.ThreadID = T1.ThreadID
INNER JOIN Table2 T2 ON T0.UserID = T2.UserID
INNER JOIN Table3 T3 ON T3.ID = T0.ID
WHERE  T0.UserID != 9149 
GROUP BY U.Name, M.ThreadID 
ORDER BY 3 DESC

You could use min() or any other aggregate function

Answer 2

I see this question all the time. You will have to decide which of those values you want. As @James Hill pointed out you have requested all distinct column sets and that is exactly what you got.

If you don't really want distinct combinations of those values you will need to specify which one to drop. Commonly people will take MAX or MIN values. Typically it looks something like this:

SELECT DISTINCT T2.Name, T0.ThreadID, MAX((CASE WHEN T0.Status = 0 THEN 1 ELSE 0 END)) AS Highlight
FROM Messaging T0
INNER JOIN Table1 T1 ON T0.ThreadID = T1.ThreadID
INNER JOIN Table2 T2 ON T0.UserID = T2.UserID
INNER JOIN Table3 T3 ON T3.ID = T0.ID
WHERE  T0.UserID != 9149 
GROUP BY U.Name, M.ThreadID 
ORDER BY Highlight DESC

You will have to test how that works with the case statement in there, but this is the general idea.