Return last messages between users

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 :)
LVL 1
webressursAsked:
Who is Participating?
 
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
of course, try both ways yourself to confirm
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
awking00Commented:
What does this do?
 AND DeletedIn = (Case when [To] = @To Then '0' else '0' END)
Why not
AND DeletedIn = '0' ?
0
 
webressursAuthor Commented:
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
 
awking00Commented:
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
 
webressursAuthor Commented:
awking00: I will check this if-clause :)

Anyway, the question is about showing last messages between users...
0
 
webressursAuthor Commented:
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
 
webressursAuthor Commented:
awking00:

I guess the if-statement should be like this:

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

:)
0
 
awking00Commented:
That makes more sense :-)
0
 
sdstuberCommented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
webressursAuthor Commented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.