how can i write this query
-
i have what i think is a fairly simple query need - but still a bit beyond me
i have a table called message
messageid, authorid, recipientid, messagetext, creationdate
i want to display a results set where only the most recent message from each author to a given recipient is displayed.
so let's say I am 'fred'. i want the most recent message from each person who has sent to me.
------------------- messages to fred
from message date
charles hey buddy 3:24pm
beth what's up?! 3:22pm
alex get my message? 2:18pm
---------------------
So Beth could have sent many messages after alex, but only the last one is shown. And the final result set is sorted by time, desc.
I can get this far:
select select AuthorId, max(creationdate) as creationdate
from message where recipientid = @recipient
group by authorid
order by creationdate desc
But how to get the messagetext in there?
thanks for any help.
-
I see for some reason my previous reply didn't get posted.
In the past I've been careful to include a RecordsID field in these sort of tables. The Records ID is set to the table Identity field and therefore this number auto-increments for each new record giving it an unique and time aware ID.
There is probably no reason why you can't add such a field to your table now. Once this field is added then the query simply becomes:
select AuthorID, creationdate, messagetext
from messages where recipientid = @recipient
and recordID
in
(
select Max(RecordID)
from
Messages
group by Authorid
)
Good Hunting!
-
I've just written this, and then just read Dan's post. I've assumed messageid is an identity, but have not assumed records are entered in date order.
I guess use the one where the assumptions match your reality.
--data
declare @message table (messageid int identity(1, 1), authorid varchar(10), recipientid varchar(10),
messagetext varchar(20), creationdate datetime)
insert @message
select 'charles', 'fred', 'hey buddy', '20060717 3:24pm'
union all select 'beth', 'fred', 'what''s up?!', '20060717 3:22pm'
union all select 'beth', 'fred', 'a', '20060717 2:22pm'
union all select 'beth', 'fred', 'b', '20060717 2:25pm' --note: inserted out of time order
union all select 'alex', 'fred', 'c', '20060717 1:18pm'
union all select 'alex', 'fred', 'd', '20060717 2:18pm' --note: same time as latest, but we don't want it
union all select 'alex', 'fred', 'get my message?', '20060717 2:18pm'
--calculation
select authorid as 'from', messagetext as 'message', creationdate as 'date'
from @message a
where messageid in
(select top 1 messageid from @message where authorid = a.authorid
order by creationdate desc, messageid desc)
/*results
from message date
---------- -------------------- ------------------------------------------------------
charles hey buddy 2006-07-17 15:24:00.000
beth what's up?! 2006-07-17 15:22:00.000
alex get my message? 2006-07-17 14:18:00.000
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Assuming (stupidly I know) that the MessageId is an identity column and is the primary key this is how I'd tackle it:
select
t.AuthorId,t.MessageText,s.MessageId,t.Received
from
Messages t
join
(select max(MessageId) as MessageId
from Messages s group by AuthorId) as s on t.MessageId = s.MessageId
Good luck
s
-
You guys rock.
Thanks - i think all of your versions would probably work but ended up using this:
select
t.AuthorId,t.MessageText,s.MessageId,t.creationdate
from
p5_Message t join
(
select max(MessageId) as MessageId
from
p5_Message s group by AuthorId)
as
s on t.MessageId = s.MessageId
where
t.RecipientId = @RecipientId
order by t.creationdate
Works like a charm.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply