Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Return last messages between users

Posted on 2013-05-21
14
Medium Priority
?
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 74

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

Expert Comment

by:sdstuber
ID: 39185152
of course, try both ways yourself to confirm
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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 74

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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