Avatar of Big Monty
Big MontyFlag for United States of America

asked on 

need assistance with sql query

in my web application, I have a feature where users can message one another. In their "inbox", I currently have a list of all of their message records displayed in the following format:

FROM            SUBJECT                                             DATE
User A           re: subject for msg1                       10-9-2015
User B           re: subject for msg1                       10-8-2015
User A           re: subject for msg1                       10-7-2015
User B           re: subject for msg1                       10-6-2015
User A           re: subject for msg2                       10-9-2015
User C           re: subject for msg2                       10-7-2015
User A           re: subject for msg2                       10-4-2015
User C           re: subject for msg2                       10-1-2015


Instead of having each message displayed in my table, I want to just have one version of each message, similar to how gMail displays messages. so it would look like:

User B           re: subject for msg1                       10-9-2015
User A           re: subject for msg2                       10-9-2015

the latest version would be what displays. For my table structure, I have a table named tblMessages that contains a messageID column, which is unique to that message, as well as a conversationID column, which is how I group messages between two users together. Right now the following query gives me ALL messages for one user:

select id = messageID, 
         starred = case starred when 1 then 'starred' else 'not starred' end, 
         [from] = displayName, 
         subject = msgSubject,
         [date] = sentDate, 
         beenRead = case beenRead when 1 then '1' else '0' end 
from tblMessages m 
         inner join tblUsers u on m.msgFrom = u.userID and u.status = 1 
where msgTo = 100 and 
        to_deleteDate is null 
order by sentDate DESC

Open in new window


how can I tweak it to return the latest message for each conversation?
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Ryan Chong
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Big Monty
Big Monty
Flag of United States of America image

ASKER

thx for such a speedy response, much appreciated!

This mostly worked, I only had to change

row_number() over (partition by messageID order by sentDate desc) idx

to

row_number() over (partition by conversationID order by sentDate desc) idx

to get the data I wanted. Thanks again!
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

coool, glad that could make some helps here cheers
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo