CODEWITHSUNDEEP
sqlsql-serverselectgroup-bydistinct
Matheus Lima
Matheus Lima

Reputation: 2143

SELECT DISTINCT with two columns

I need to distinct both SenderId, and RecipientId.

So I did this:

SELECT DISTINCT M.SenderId, R.StudentId as RecipientId
FROM Message M (nolock) INNER JOIN Recipient R (nolock) ON M.Id=R.MessageId 
GROUP BY M.SenderId, R.StudentId
HAVING StudentId=1 OR SenderId=1

And this works, but I also need the M.Text field there, but without the distinct. So I added this:

GROUP BY M.SenderId, R.StudentId, M.Text

But this does not work.

Upvotes: 0

Views: 136

Answers (2)

anon
anon

Reputation:

If I've understood your question properly, this will group what you want and distinct SenderId, and StudentId:

SELECT M.SenderId, R.StudentId as RecipientId, M.Text
FROM Message M (nolock) INNER JOIN Recipient R (nolock) ON M.Id=R.MessageId 
GROUP BY M.SenderId, R.StudentId, M.Text
HAVING COUNT(StudentId) = 1 OR COUNT(SenderId) = 1

Upvotes: 0

JohnLBevan
JohnLBevan

Reputation: 24470

Here's some options; not sure from the wording which best suits your requirements, but suspect one will...

--selects unique combination of sender, recipient and text
--meaning the combo of 3 is unique, but within that combo values
--in each individual column may be repeated

SELECT DISTINCT M.SenderId
, R.StudentId as RecipientId
, M.Text
FROM Message M (nolock) 
INNER JOIN Recipient R (nolock) ON R.MessageId = M.Id
where StudentId=1 
or SenderId=1

or

--returns all unique combos of SenderId and RecipientId
--along with a single corresponding Text field
--max() is just an arbitrary aggregate function to ensure we only
--get 1 result for M.Text

SELECT M.SenderId
, R.StudentId as RecipientId
, max(M.Text)
FROM Message M (nolock) 
INNER JOIN Recipient R (nolock) ON R.MessageId = M.Id
where StudentId=1 
or SenderId=1
group bu M.SenderId
, R.StudentId

Upvotes: 2

Related Questions