Claudiu10
asked on
SQL selection query question
Hi,
I was wondering if anyone can tell me how to do the following using sql transact for sql server 2008.
I want to select all records in a couple of linked tables where UserId = @UserId AND ThreadId has to be the most recent one and has to be distinct.
For example user can have many threads but also a lot of messages on the same thread. On the main page I just want to see the most recent message in that thread plus all the other most recent message in different threads.
Thank you
I was wondering if anyone can tell me how to do the following using sql transact for sql server 2008.
I want to select all records in a couple of linked tables where UserId = @UserId AND ThreadId has to be the most recent one and has to be distinct.
For example user can have many threads but also a lot of messages on the same thread. On the main page I just want to see the most recent message in that thread plus all the other most recent message in different threads.
Thank you
you need to tell us the structure of your tables and the volumes involved ....
however
however
select x.Threadid
,x.ThreadTopic
,x.msgid
,x.msgresponse
,x.msgtime
,x.msguser
from (select the columns that you want....
,row_number() over (partition by t.threadid order by m.msgtime desc) as rn
-- could be a msg sequence number instead of the msg date and time ... whatever indicates
-- the latest messages should be in the above order by xxxxx desc
from threads as T
left outer join Messages as M
on t.threadid=m.threadid
where t.userid=@userid
) as X
where rn=1
order by threadid,....
ASKER
HI,
thanks for the info. I tried this. Any idea why it's giving me incorrect column rownum?
SELECT (ow_number() over (PARTITION by t.ThreadId order by m.CreateDate DESC) AS rownum, t.ThreadId, t.ThreadSubject, m.MessageId, m.SentByUserId, m.Body, m.CreateDate, m.MessageTypeId, r.UserId, r.MessageStatusTypeId, r.DateLastModified
FROM Messages AS m INNER JOIN
MessageThread AS t ON m.ThreadId = t.ThreadId INNER JOIN
MessageRecipients AS r ON m.MessageId = r.MessageId
WHERE rownum = 1 AND r.UserId = @UserId
Thank you
thanks for the info. I tried this. Any idea why it's giving me incorrect column rownum?
SELECT (ow_number() over (PARTITION by t.ThreadId order by m.CreateDate DESC) AS rownum, t.ThreadId, t.ThreadSubject, m.MessageId, m.SentByUserId, m.Body, m.CreateDate, m.MessageTypeId, r.UserId, r.MessageStatusTypeId, r.DateLastModified
FROM Messages AS m INNER JOIN
MessageThread AS t ON m.ThreadId = t.ThreadId INNER JOIN
MessageRecipients AS r ON m.MessageId = r.MessageId
WHERE rownum = 1 AND r.UserId = @UserId
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help. Appreciate it.
ASKER
Hi,
Actually there is a problem in my case with this statement.
I am trying to get all the messages received by a user by the following criteria:
1. Different thread.
2. Newest
3. The user getting the messages can't be the sender.
Problems with this query:
The partitioning does not guarantee that rownum 1 doesn't contain the user reading the messages as the sender. I don't see a way to put in a where clause in the partition. Any ideas how I can make that happen?
Actually there is a problem in my case with this statement.
I am trying to get all the messages received by a user by the following criteria:
1. Different thread.
2. Newest
3. The user getting the messages can't be the sender.
Problems with this query:
The partitioning does not guarantee that rownum 1 doesn't contain the user reading the messages as the sender. I don't see a way to put in a where clause in the partition. Any ideas how I can make that happen?
ASKER
I also tryed partitioning by senders in a second rownumber but that doesn't guarantee the case where there is more than one thread by the same sender.
ASKER
sorry think I got it.
added the following in the where clause at the end of the query before the closing bracket.
WHERE m.SentByUserId <> @UserId AND r.UserId = @UserId
added the following in the where clause at the end of the query before the closing bracket.
WHERE m.SentByUserId <> @UserId AND r.UserId = @UserId
Are you still looking for any help?
ASKER
no I got it thanks. I was making a mistake by putting the where clause after the closing bracket initially.
Thank you
Thank you
Open in new window