Solved

Return last messages between users

Posted on 2013-05-21
14
261 Views
Last Modified: 2013-05-30
I have a table that contains messages between users. The SQL statement below returns all active messages to or from one spesific user (@To='James', @From='James'):

SELECT Id, Title, [Text], [From], [To] FROM tblUserMessage
WHERE ([To] = @To OR [From] = @From) AND (SiteId = @SiteId) AND (Deleted = '0') AND DeletedIn = (Case when [To] = @To Then '0' else '0' END) AND DeletedOut = (Case when [From] = @From Then '0' else '0' END)
ORDER BY Id DESC

Open in new window


Result:

Id    Title     Text     From    To
10    Title10  Text10  James   Kirk
9     Title9   Text9   James   Kirk
8     Title8   Text8   Kirk    James
7     Title7   Text7   James   Lars
6     Title6   Text6   Kirk    James
5     Title5   Text5   Lars    James
4     Title4   Text4   Lars    James
3     Title3   Text3   James   Lars
2     Title2   Text2   James   Kirk
1     Title1   Text1   Kirk    James

Open in new window


What I need to do is to only return the last message between each user. Basted on the result above, the correct result should be like this (@To='James', @From='James'):

Id    Title     Text     From    To
10    Title10  Text10  James   Kirk
8     Title8   Text8   Kirk    James
7     Title7   Text7   James   Lars
5     Title5   Text5   Lars    James

Open in new window


Hope someone can help me solving this :)
0
Comment
Question by:webressurs
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39185128
select Id, Title, [Text], [From], [To] from
(SELECT Id, Title, [Text], [From], [To] , row_number() over(partition by [From],[To] order by id desc) rn
FROM tblUserMessage
WHERE ([To] = @To OR [From] = @From) AND (SiteId = @SiteId) AND (Deleted = '0') AND DeletedIn = (Case when [To] = @To Then '0' else '0' END) AND DeletedOut = (Case when [From] = @From Then '0' else '0' END)) as x
where rn = 1
ORDER BY Id DESC

i recommend using analytics (row_number) over subqueries that hit the same table twice

"select * from table where x in (select max from table)"

even if indexed, the analytic will still likely perform better, simply because it only needs to read once
0
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
ID: 39185135
WITH MyUserMessage (Id, Title, [Text], [From], [To])
AS
(
    SELECT Id, Title, [Text], [From], [To] FROM tblUserMessage
    WHERE ([To] = @To OR [From] = @From) AND (SiteId = @SiteId) AND (Deleted = '0') AND DeletedIn = (Case when [To] = @To Then '0' else '0' END) AND DeletedOut = (Case when [From] = @From Then '0' else '0' END)
)
SELECT Id, Title, [Text], [From], [To]
FROM   MyUserMessage
WHERE  Id IN (SELECT MAX(Id) FROM MyUserMessage
              GROUP BY [From], [To])
ORDER BY Id DESC

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39185152
of course, try both ways yourself to confirm
0
 
LVL 31

Expert Comment

by:awking00
ID: 39185283
What does this do?
 AND DeletedIn = (Case when [To] = @To Then '0' else '0' END)
Why not
AND DeletedIn = '0' ?
0
 
LVL 1

Author Comment

by:webressurs
ID: 39185404
awking00:

AND (Deleted = '0') AND DeletedIn = (Case when [To] = @To Then '0' else '0' END) AND DeletedOut = (Case when [From] = @From Then '0' else '0' END)

-> dont show messages to me if I have deleted them in my inbox. They shall still be visible in senders outbox.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39185609
Perhaps I am missing something, but all I was trying to say was that it appears you're making a comparison in the case when some expression equals another expression then compare to  0 or when some expression does not equal another expression then also compare to 0, which is the same as comparing to 0 directly.
0
 
LVL 1

Author Comment

by:webressurs
ID: 39185717
awking00: I will check this if-clause :)

Anyway, the question is about showing last messages between users...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:webressurs
ID: 39186957
Dsacker:

I used your code, and it worked just fine :)

There is only one more thing, the stored procedure need paging. After modifying your code I solved this by my selves. I don't know if it can be done better to increase the performance. Here is my final code with paging, can you (or someone else) please take a look at it and see if some changes should be done?

WITH MyUserMessage (Id, [From], [To]) -- All messages
AS
(
	SELECT Id, [From], [To] FROM tblUserMessage
	WHERE ([To] = @To OR [From] = @From) AND (SiteId = @SiteId) AND (Deleted = '0') AND DeletedIn = (Case when [To] = @To Then '0' else '0' END) AND DeletedOut = (Case when [From] = @From Then '0' else '0' END)		
)

INSERT INTO #PageIndexForUsers(MessageId) -- All grouped messages
	SELECT Id FROM tblUserMessage
	WHERE Id IN (SELECT MAX(Id) FROM MyUserMessage GROUP BY [From], [To])
	ORDER BY Id DESC


SELECT u.Id, u.Title, u.[Text], u.[From], u.[To] 
FROM tblUserMessage u, #PageIndexForUsers p
WHERE u.Id = p.MessageId 
AND	p.IndexId >= @Lower AND p.IndexId <= @Upper 
AND u.DeletedIn = (Case when [To] = @To Then '0' else '0' END) AND u.DeletedOut = (Case when [From] = @From Then '0' else '0' END)
ORDER BY u.Id desc

SELECT @TotalMessages = COUNT(*) FROM #PageIndexForUsers
DROP TABLE #PageIndexForUsers

Open in new window

0
 
LVL 1

Author Comment

by:webressurs
ID: 39187469
awking00:

I guess the if-statement should be like this:

...AND DeletedIn = (Case when [To] = @To Then '0' else DeletedIn END)

:)
0
 
LVL 31

Expert Comment

by:awking00
ID: 39187479
That makes more sense :-)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39187494
to increase performance,  try using the analytics in the first post instead of the double-query method for the insert

and probably some form of nested TOP queries for the paged select
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39187828
I'm really not sure you needed to do anything beyond the code I sent you. To get the TotalMessages, you could have simply done the following:
DECLARE @TotalMessages int;

WITH MyUserMessage (Id, Title, [Text], [From], [To])
AS
(
    SELECT Id, Title, [Text], [From], [To] FROM tblUserMessage
    WHERE ([To] = @To OR [From] = @From) AND (SiteId = @SiteId) AND (Deleted = '0') AND DeletedIn = (Case when [To] = @To Then '0' else '0' END) AND DeletedOut = (Case when [From] = @From Then '0' else '0' END)
)
SELECT Id, Title, [Text], [From], [To]
FROM   MyUserMessage
WHERE  Id IN (SELECT MAX(Id) FROM MyUserMessage
              GROUP BY [From], [To])
ORDER BY Id DESC;

SET @TotalMessages = @@ROWCOUNT
SELECT @TotalMessages

Open in new window

0
 
LVL 1

Author Comment

by:webressurs
ID: 39188449
Dsacker: Mabye my explanation was bad, but I also need pagination in the stored procedure. Then I can have inputs like @PageSize and @PageNumber to return just the records I want. Please see the complete code below, it works but I'm not sure if it can be done better to increase the performance?

ALTER PROCEDURE [dbo].[spGetMessages] 
(
	@To varchar(256),
	@From varchar(256),
	@SiteId smallint,
	@PageNumber int,
	@PageSize int,
	@TotalMessages int OUTPUT
)
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @Lower int
	DECLARE @Upper int
	DECLARE @Total int
	
	SET @Lower = @PageSize * @PageNumber
	SET @Upper = @PageSize - 1 + @Lower

	--temp table
	CREATE TABLE #PageIndexForUsers
	(
		IndexId int IDENTITY (0,1) NOT NULL,
		MessageId int
	)

	IF @To <> '' AND @From <> ''
	BEGIN

		WITH MyUserMessage (Id, [From], [To]) -- All messages to or from user
		AS
		(
			SELECT Id, [From], [To] FROM tblUserMessage
			WHERE ([To] = @To OR [From] = @From) AND (SiteId = @SiteId) AND (Deleted = '0') AND DeletedIn = (Case when [To] = @To Then '0' else DeletedIn END) AND DeletedOut = (Case when [From] = @From Then '0' else DeletedOut END)		
		)

		INSERT INTO #PageIndexForUsers(MessageId) -- All grouped messages
			SELECT Id FROM tblUserMessage
			WHERE Id IN (SELECT MAX(Id) FROM MyUserMessage GROUP BY [From], [To])
			ORDER BY Id DESC


		SELECT u.Id, u.Title, u.[Text], u.[From], u.[To] 
		FROM tblUserMessage u, #PageIndexForUsers p
		WHERE u.Id = p.MessageId 
		AND	p.IndexId >= @Lower AND p.IndexId <= @Upper 
		ORDER BY u.Id desc

		SELECT @TotalMessages = COUNT(*) FROM #PageIndexForUsers
		DROP TABLE #PageIndexForUsers


	END

END

Open in new window

0
 
LVL 20

Expert Comment

by:dsacker
ID: 39188461
Honestly, while there is more than one way to skin that cat, I don't see anything in your solution that will cause a serious performance issue. If it's working, lock n load. :)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now