ROW number with distinct - SQL Server Forums

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)

ROW number with distinct

Author

Topic

gxs8938
Starting Member

23 Posts

Posted - 2010-01-27 : 12:12:44

Hello All,

I have a query set up like below.

WITH tempLog AS (SELECT ROW_NUMBER() OVER (ORDER BY m.threadid DESC)AS Row,
m.[threadId] as threadid
, m.[subject] as subject
, m.[datecre] as datecre
, rm.[replyMessage] as lastmessage
, rm.[author] as sender
, rm.[datereplied] as date
FROM
message m

OUTER APPLY (
SELECT TOP 1
rmes.[replyMessage] AS [replyMessage]
, rmes.[author] AS [author]
, rmes.[dateReplied] AS [dateReplied]
FROM
replyMessage rmes
WHERE
rmes.[threadID] = m.[threadID]

ORDER BY
rmes.[dateReplied] Desc
)
rm where m.authorID = 67)SELECT distinct threadid,subject,
rs.datecre as datecre,lastmessage,sender,date
FROM tempLog outer apply(select top 1 datecre from templog s
where templog.threadid=s.threadid order by datecre desc )rs
WHERE Row between ((1- 1) * 10 + 1) and (1*10)
order by threadid desc

At the last query i need to select distinct rows but as i specify rownum. for instance as i request 10 rows the distinct eliminates duplicates and i get inly few rows(Instead of actual 10 rows).

If I do not use distinct then it gives me exact 10 rows with duplicates.Any turn around??

Regards,
Guru

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 12:16:14

sorry your problem is not clear. can you post some sample data and explain?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 12:20:05

may be what you're looking at is this??


WITH tempLog AS (SELECT DISTINCT
m.[threadId] as threadid
, m.[subject] as subject
, m.[datecre] as datecre
, rm.[replyMessage] as lastmessage
, rm.[author] as sender
, rm.[datereplied] as date
FROM
message m

OUTER APPLY (
SELECT TOP 1
rmes.[replyMessage] AS [replyMessage]
, rmes.[author] AS [author]
, rmes.[dateReplied] AS [dateReplied]
FROM
replyMessage rmes
WHERE
rmes.[threadID] = m.[threadID]

ORDER BY
rmes.[dateReplied] Desc
)
rm where m.authorID = 67)

SELECT threadid,subject,
datecre,lastmessage,sender,date
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY m.threadid DESC)AS Row,
threadid,subject,
rs.datecre as datecre,lastmessage,sender,date
FROM tempLog 
outer apply(select top 1 datecre from templog s 
where templog.threadid=s.threadid order by datecre desc )rs
)t
WHERE Row between ((1- 1) * 10 + 1) and (1*10)
order by threadid desc

gxs8938
Starting Member

23 Posts

Posted - 2010-01-27 : 12:21:16

Ok here it goes In the final part of select statement if i do not specify distinct it gives me 10 rows as specified in the where clause.

299,ddd,2010-01-27 00:47:48.010,NULL,NULL,NULL
299,ddd,2010-01-27 00:47:48.010,NULL,NULL,NULL
299,ddd,2010-01-27 00:47:48.010,NULL,NULL,NULL
299,ddd,2010-01-27 00:47:48.010,NULL,NULL,NULL
298,gg,2010-01-26 23:47:42.407,NULL,NULL,NULL
298,gg,2010-01-26 23:47:42.407,NULL,NULL,NULL
297,tt,2010-01-26 23:13:03.860,NULL,NULL,NULL
297,tt,2010-01-26 23:13:03.860,NULL,NULL,NULL
295,t,2010-01-26 20:16:48.930,NULL,NULL,NULL
252,subject,2010-01-22 11:55:38.213,Message adn photo,me@home.net,2010-01-22 12:04:13.010

But when i use distinct instead of 10 rows i get 5 rows.SO i cannot use distinct along with row num. any other alternative ??

299 ddd 2010-01-27 00:47:48.010 NULL NULL NULL
298 gg 2010-01-26 23:47:42.407 NULL NULL NULL
297 tt 2010-01-26 23:13:03.860 NULL NULL NULL
295 t 2010-01-26 20:16:48.930 NULL NULL NULL
252 subject 2010-01-22 11:55:38.213 Message adn photo me@home.net 2010-01-22 12:04:13.010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 12:24:16

quote:
Originally posted by gxs8938

Ok here it goes In the final part of select statement if i do not specify distinct it gives me 10 rows as specified in the where clause.

299,ddd,2010-01-27 00:47:48.010,NULL,NULL,NULL
299,ddd,2010-01-27 00:47:48.010,NULL,NULL,NULL
299,ddd,2010-01-27 00:47:48.010,NULL,NULL,NULL
299,ddd,2010-01-27 00:47:48.010,NULL,NULL,NULL
298,gg,2010-01-26 23:47:42.407,NULL,NULL,NULL
298,gg,2010-01-26 23:47:42.407,NULL,NULL,NULL
297,tt,2010-01-26 23:13:03.860,NULL,NULL,NULL
297,tt,2010-01-26 23:13:03.860,NULL,NULL,NULL
295,t,2010-01-26 20:16:48.930,NULL,NULL,NULL
252,subject,2010-01-22 11:55:38.213,Message adn photo,me@home.net,2010-01-22 12:04:13.010

But when i use distinct instead of 10 rows i get 5 rows.SO i cannot use distinct along with row num. any other alternative ??

299 ddd 2010-01-27 00:47:48.010 NULL NULL NULL
298 gg 2010-01-26 23:47:42.407 NULL NULL NULL
297 tt 2010-01-26 23:13:03.860 NULL NULL NULL
295 t 2010-01-26 20:16:48.930 NULL NULL NULL
252 subject 2010-01-22 11:55:38.213 Message adn photo me@home.net 2010-01-22 12:04:13.010