Solved

Return last messages between users

Posted on 2013-05-21
14
263 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help on t-sql 2012 10 53
string fuctions 4 25
Run SQL Server Proc from Access 11 28
New to SSRS, extremely slow running report. 8 19
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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

785 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