Solved

Return last messages between users

Posted on 2013-05-21
14
262 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 32

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 32

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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 32

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

896 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

13 Experts available now in Live!

Get 1:1 Help Now