need to get the last instance of the query

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

need to get the last instance of the query

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-25 : 18:25:45

ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
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 B.[p_Primary] = '1'
AND A.[TimeStamp] = (SELECT MAX(C.[TimeStamp])
from tbl_ProfileViews C Where C.ViewUserNameID = @ViewedUsernameID)
ORDER BY A.[TimeStamp] DESC

I am getting many intances p_Primary is the main image if 1 then there is an image.
Other

I should get one instance if there are no primary images that is the last dated one.

31 44 2011-01-24 17:22:50.023 44 31 0
------------------------------------------------------------

31 44 2011-01-24 17:22:50.023 40 31 0
31 44 2011-01-24 17:22:50.023 41 31 0
31 44 2011-01-24 17:22:50.023 42 31 0
31 44 2011-01-24 17:22:50.023 43 31 0
31 44 2011-01-24 17:22:50.023 44 31 0
31 44 2011-01-24 17:22:50.023 47 31 1

Any help would be awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 11:06:01

do you mean this?


ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
SELECT TOP 100 ViewerUserNameID,
ViewedUserNameID,
ViewedDate,
PhotoID,
ProfileID,
p_Primary
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.[UserNameID],A.[ViewUserNameID],A.[TimeStamp] ORDER BY CASE WHEN B.[p_Primary] = 1 THEN 0 ELSE B.[PhotoID] END DESC) AS rn,
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 
)t
WHERE rn=1 OR 
[p_Primary] = '1'
ORDER BY [TimeStamp] DES

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-26 : 12:03:51

Msg 102, Level 15, State 1, Procedure prc_ProfileViews_SelectByUserName, Line 29
Incorrect syntax near 'DES'.

[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

I need to return the last instance regardless p_primary = 1
This is not necessary.

ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
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

31 44 2011-01-24 17:22:50.023 40 31 0
31 44 2011-01-24 17:22:50.023 41 31 0
31 44 2011-01-24 17:22:50.023 42 31 0
31 44 2011-01-24 17:22:50.023 43 31 0
31 44 2011-01-24 17:22:50.023 44 31 0
31 44 2011-01-24 17:22:50.023 47 31 0
45 44 2011-01-20 11:36:12.940 50 45 1
47 44 2011-01-20 11:34:24.320 52 47 1

I need to return this.
31 44 2011-01-24 17:22:50.023 47 31 0
45 44 2011-01-20 11:36:12.940 50 45 1
47 44 2011-01-20 11:34:24.320 52 47 1

Any help would be awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 12:11:34

thats just a typo . make last statement as

ORDER BY [TimeStamp] DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-26 : 12:33:03

Did you look at the last query.

I need help on fixing that.

p_primary is not necessary

Any help would be awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 12:37:36

[code]
ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
SELECT TOP 100 ViewerUserNameID,
ViewedUserNameID,
ViewedDate,
PhotoID,
ProfileID,
p_Primary
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.[UserNameID],A.[ViewUserNameID],A.[TimeStamp] ORDER BY B.[PhotoID] DESC) AS rn,
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
)t
WHERE rn=1
ORDER BY [TimeStamp] DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-26 : 13:00:52

Msg 207, Level 16, State 1, Procedure prc_ProfileViews_SelectByUserName, Line 28
Invalid column name 'TimeStamp'.

Any help would be awesome.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-26 : 14:00:57

ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
SELECT TOP 100 ViewerUserNameID,
ViewedUserNameID,
ViewedDate,
PhotoID,
ProfileID,
p_Primary
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.[UserNameID],A.[ViewUserNameID],A.[TimeStamp] ORDER BY B.[PhotoID] DESC) AS rn,
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
)t
WHERE rn=1
ORDER BY ViewedDate DESC

I am getting the following without the max date

31 44 2011-01-24 17:22:50.023 47 31 1
31 44 2011-01-24 09:55:35.610 47 31 1
45 44 2011-01-20 11:36:12.940 50 45 1
47 44 2011-01-20 11:34:24.320 52 47 1
31 44 2011-01-18 21:04:26.860 47 31 1
49 44 2011-01-18 14:27:18.370 64 49 0
49 44 2011-01-18 14:21:54.603 64 49 0
47 44 2011-01-13 14:50:42.130 52 47 1
47 44 2011-01-13 11:47:04.007 52 47 1
47 44 2011-01-13 11:47:00.537 52 47 1
47 44 2011-01-13 11:45:50.373 52 47 1
31 44 2011-01-10 15:39:02.077 47 31 1
31 44 2011-01-07 20:54:42.763 47 31 1
31 44 2011-01-07 20:39:55.453 47 31 1
31 44 2011-01-07 20:36:02.497 47 31 1

Any help would be awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-27 : 12:13:14

so your date also varies? this was not as per initial sample posted


ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
SELECT TOP 100 ViewerUserNameID,
ViewedUserNameID,
ViewedDate,
PhotoID,
ProfileID,
p_Primary
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.[UserNameID],A.[ViewUserNameID] ORDER BY A.[TimeStamp] DESC) AS rn,
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 
)t
WHERE rn=1 
ORDER BY ViewedDate DESC