Distinct used along with rownum does not work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct used along with rownum does not work

Author

Topic

gxs8938
Starting Member

23 Posts

Posted - 2010-01-19 : 16:00:02

Hello ALL,

Below is my Stored procedure
ALTER PROCEDURE [dbo].[ConversationsIndex]
@AuthorID int,
@currentPage INT,
@pageSize INT

AS
BEGIN
set nocount on;

WITH tempLog AS (
SELECT distinct ROW_NUMBER() OVER (ORDER BY message.threadid DESC)AS Row,
message.threadid,message.subject,datecre,Replymessage as lastmessage,Author as sender,
datereplied as date
from message left outer join ReplyMessage on authorid = @AuthorID and message.threadid=replymessage.threadid
)

SELECT threadid,subject,
datecre,lastmessage,sender,date
FROM tempLog
WHERE Row between ((@currentPage - 1) * @pageSize + 1) and (@currentPage*@pageSize)

END
-----------------------------------------------------------------

Distinct Keyword has no effect when used along with rownumber()

Can anyone point me how SQL engine optimizes it??

Regards,
Guru

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-19 : 16:12:06

Your Row_Number will never be duplicate, therefore you will never remove any rows. create a derived table with the distinct rows first and then do a Row number over it, or use a CTE which is your distinct rows.

gxs8938
Starting Member

23 Posts

Posted - 2010-01-21 : 01:16:20

Thanks a ton. I got it solved.

Regards,
Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources