Solved

SQL selection query question

Posted on 2011-02-27
10
298 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Claudiu10
  • 6
  • 3
10 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34993993
try like this.
select * 
  from (
select *,row_number() over (partition by UserID order by ThreadID desc) rn
  from your_table) t1
 where rn = 1 and UserID = @UserID

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34994009
you need to tell us the structure of your tables and the volumes involved ....

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,....

Open in new window

0
 

Author Comment

by:Claudiu10
ID: 35004083
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
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35004251
try like this.
SELECT * 
  FROM (SELECT ROW_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) t1 
 WHERE rownum = 1 
       AND UserId = @UserId

Open in new window

0
 

Author Closing Comment

by:Claudiu10
ID: 35014115
Thanks for your help.  Appreciate it.
0
 

Author Comment

by:Claudiu10
ID: 35014447
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?
0
 

Author Comment

by:Claudiu10
ID: 35014454
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.
0
 

Author Comment

by:Claudiu10
ID: 35014480
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
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35014492
Are you still looking for any help?
0
 

Author Comment

by:Claudiu10
ID: 35014633
no I got it thanks.  I was making a mistake by putting the where clause after the closing bracket initially.

Thank you
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql Total query 2 26
get column names from table in vb.net 8 26
convert null in sql server 12 32
Whats wrong in this query - Select * from tableA,tableA 11 29
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question