duplicate results - distinct clause not helping

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)

duplicate results - distinct clause not helping

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-14 : 08:37:30

Hi,

I have a query I am trying to work, but the data is not being brought back as desired.
I don't believe I am using the DISTINCT operator correctly, as I am getting back duplicate "viewerUserID" columns.

Any insight on how I can fix this ?

Thanks once again!
Mike123

SELECT distinct(viewerUserID) ,UD.nameOnline,viewDate

FROM [dbo].[tblProfileViews_Users]
PV JOIN tblUserDetails UD on UD.userID = PV.viewerUserID

WHERE profileUserID = @userID

AND viewerUserID NOT IN (SELECT profileUserID FROM [dbo].[tblProfileViews_Users] WHERE viewerUserID = @userID)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 08:39:56

Use the ROW_NUMBER technique posted to you before.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 08:44:00

[code]
SELECT reqd columns here
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY viewerUserID ORDER BY viewDate) AS Seq, viewerUserID ,UD.nameOnline,viewDate

FROM [dbo].[tblProfileViews_Users] PV
PV JOIN tblUserDetails UD on UD.userID = PV.viewerUserID
LEFT JOIN [dbo].[tblProfileViews_Users] pv1
ON pv1.profileUserID =PV.viewerUserID
AND viewerUserID = @userID
WHERE PV.profileUserID = @userID
AND pv1.profileUserID IS NULL
)t
WHERE Seq=1
[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-14 : 09:17:56

Hey Visakh16,

My versions executes in just a second. So far your version has taken 6 minutes, I had to add in table prefixes as it wouldn't run before. I don't think I messed anything up?

thanks again!
mike123

SELECT viewerUserID, nameOnline,viewDate
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY PV.viewerUserID ORDER BY PV.viewDate) AS Seq, PV.viewerUserID ,UD.nameOnline,PV.viewDate

FROM [dbo].[tblProfileViews_Users] PV
JOIN tblUserDetails UD on UD.userID = PV.viewerUserID
LEFT JOIN [dbo].[tblProfileViews_Users] pv1
ON pv1.profileUserID =PV.viewerUserID
AND PV.viewerUserID = 10000
WHERE PV.profileUserID = 10000
AND pv1.profileUserID IS NULL
)t
WHERE Seq=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 09:22:37

quote:
Originally posted by mike123

Hey Visakh16,

My versions executes in just a second. So far your version has taken 6 minutes, I had to add in table prefixes as it wouldn't run before. I don't think I messed anything up?

thanks again!
mike123

SELECT viewerUserID, nameOnline,viewDate
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY PV.viewerUserID ORDER BY PV.viewDate) AS Seq, PV.viewerUserID ,UD.nameOnline,PV.viewDate

FROM [dbo].[tblProfileViews_Users] PV
JOIN tblUserDetails UD on UD.userID = PV.viewerUserID
LEFT JOIN [dbo].[tblProfileViews_Users] pv1
ON pv1.profileUserID =PV.viewerUserID
AND PV1.viewerUserID = 10000
WHERE PV.profileUserID = 10000
AND pv1.profileUserID IS NULL
)t
WHERE Seq=1

it should be PV1

you can also try this


SELECT viewerUserID, nameOnline,viewDate
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY PV.viewerUserID  ORDER BY PV.viewDate) AS Seq, PV.viewerUserID ,UD.nameOnline,PV.viewDate

FROM [dbo].[tblProfileViews_Users] PV
JOIN tblUserDetails UD on UD.userID = PV.viewerUserID
WHERE PV.profileUserID = 10000
AND  NOT EXISTS (SELECT 1 FROM [dbo].[tblProfileViews_Users] WHERE viewerUserID = 10000 AND profileUserID=PV.viewerUserID)
)t
WHERE Seq=1

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-14 : 09:36:52

last solution worked in < 1 second .. still couldnt get the first to work, but no longer needed...

thanks again to both of you! :)

Cheers,
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 10:08:24

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources