would like to modify query. - 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

General SQL Server Forums

New to SQL Server Programming

would like to modify query.

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-25 : 14:33:23

I have the following query which returns multiple instances of the same user. I would like to make sure that the query returns the latest date instance.
Any help would be great.
SELECT TOP 100
[UserNameID] as ViewerUserNameID, ----- table tbl_ProfileViews
[ViewUserNameID] as ViewedUserNameID, ----- table tbl_ProfileViews
[TimeStamp] as ViewedDate, ----- table tbl_ProfileViews
[PhotoID], ----- table tbl_Photos
[ProfileID], ----- table tbl_Photos
[p_Primary] ----- table tbl_Photos
FROM tbl_ProfileViews
INNER JOIN tbl_Photos ON tbl_Photos.ProfileID = tbl_profileViews.UserNameID
WHERE tbl_ProfileViews.ViewUserNameID = @ViewedUsernameID
AND (@FromDate IS NULL OR (([TimeStamp] >= @FromDate)
AND [TimeStamp] = (SELECT MAX([TimeStamp])

FROM tbl_ProfileViews A where A.UserNameID = tbl_profileViews.UserNameID)))
ORDER BY [TimeStamp] DESC

Any help would be awesome.

jp.jetti
Starting Member

3 Posts

Posted - 2011-01-25 : 16:01:22

SELECT TOP 100
A.[UserNameID] as ViewerUserNameID, ----- table tbl_ProfileViews
A.[ViewUserNameID] as ViewedUserNameID, ----- table tbl_ProfileViews
A.[TimeStamp] as ViewedDate, ----- table tbl_ProfileViews
B.[PhotoID], ----- table tbl_Photos
B.[ProfileID], ----- table tbl_Photos
B.[p_Primary] ----- table tbl_Photos
FROM tbl_ProfileViews A INNER JOIN tbl_Photos B
ON A.UserNameID = B.ProfileID
WHERE A.ViewUserNameID = @ViewedUsernameID
AND A.[TimeStamp] = (SELECT MAX(C.[TimeStamp] from tbl_ProfileViews C Where C.ViewUserNameID = @ViewedUsernameID)
ORDER BY A.[TimeStamp] DESC

I hope this will help you.
You did the most. I just did aliasing.
All the best

JP Jetti

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources