Select Distinct Question

matthias s 0

Hi there, I've got a Messages table which looks roughly like this: SenderID (int) RecipientID (int) TimeStamp (datetime) Body (varchar(512) Now I'd like to do a select which gets me (RecipientID = 1) the first message (depending on the timestamp) of each sender. Example: Sender ID 5 has sent two messages, the recordset should contain the oldest message of this fella. Sender ID 6 has sent five messages, the recordset should contain the oldest message of this guy as well. I thought this could be done by tying a DISTINCT to a column. I hoped I could do it somehow like this: SELECT DISTINCT(SenderID), TimeStamp, Body FROM Messages WHERE RecipientID = 1 ORDER BY TimeStamp DESC I was just guessing into the wild. Is there an easy way to do that? Thanks in advance!

/matthias

I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]

Paddy Boyd

Try

SELECT M.SenderId, M.TimeStamp, M.Body FROM Messages AS M
WHERE M.TimeStamp = (SELECT MIN(M1.TimeStamp) FROM Messages AS M1 WHERE M1.SenderId = M.SenderId)

Pete OHanlon

Distinct won't do this because unless the timestamp was the same for both records, then you have two distinct records. What you could do is to select based on the MIN timestamp (there are may ways to do this).

Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.

Private_Void

Assuming "Oldest" means been in the DB the Longest. This is more complex than first meets the eye due to the fact that you need to tie in the Oldest TimeStamp with its cooresponding Body field. SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body FROM Messages T1 INNER JOIN ( SELECT MIN[TimeStamp] AS MINTimeStamp ,Body FROM Messages GROUP BY Body )T2 ON T1.Body = T2.Body AND T1.[TimeStamp] = T2.[TimeStamp] WHERE RecipeintID = 1 GROUP BY T1.SenderID I did this blind, without sql around, so forgive me if it is a little off. It should be pretty close to what you are looking for if I understood what you were trying to do. SELECT T1.SenderID, MIN(TimeStamp) AS MINStamp, Body AS Body FROM @Messages T1 INNER JOIN ( SELECT GROUP BY SenderID

Private_Void

Assuming "Oldest" means been in the DB the Longest. And I understood what you are trying to do this should be pretty close. I did this without a SQL Server so forgive if a little off. This is more complex than first meets the eye due to the fact that you need to tie in the Oldest TimeStamp with its cooresponding Body field. SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body FROM Messages T1 INNER JOIN ( SELECT MIN[TimeStamp] AS MINTimeStamp ,Body FROM Messages GROUP BY Body )T2 ON T1.Body = T2.Body AND T1.[TimeStamp] = T2.[TimeStamp] WHERE RecipeintID = 1 GROUP BY T1.SenderID

Private_Void

Assuming "Oldest" means been in the DB the Longest. And I understood what you are trying to do this should be pretty close. I did this without a SQL Server so forgive if a little off. SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body FROM Messages T1 INNER JOIN ( SELECT MIN[TimeStamp] AS MINTimeStamp ,Body FROM Messages GROUP BY Body )T2 ON T1.Body = T2.Body AND T1.[TimeStamp] = T2.[TimeStamp] WHERE RecipeintID = 1 GROUP BY T1.SenderID