Stored Procedure Selection - 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

Stored Procedure Selection

Author

Topic

SteenPedersen
Starting Member

2 Posts

Posted - 2010-02-12 : 07:41:34

Hi guys
I'm having a problem with a Stored Procedure. I want to join these two tables:

TABLE: USER
ID Name Age
1 Jonn 45
2 Jane 43
3 Donald 57
4 Mickey 56

TABLE: VISITS
ID UserID LastVisit
1 1 01-01-2010
2 2 04-01-2010
3 1 17-01-2010
4 1 31-01-2010
5 4 01-02-2010
6 2 11-02-2010

For every user in the USER table, I just want to show the newest record from the VISITS table.
As far as I see, I can retrieve the newest record from the VISITS table by using something like this:

SELECT userID, MAX(LastVisit) AS LastVisit FROM Visits GROUP BY UserID

... but how do I combine this with the other table - in a Stored Procedure?

Based on the two tables above, I expect a result like this:

ID Name Age LastVisit
1 Jonn 45 31-01-2010
2 Jane 43 11-02-2010
3 Donald 57 NULL
4 Mickey 56 01-02-2010

I hope some of you guys out there can come up with a usable suggestion. Thanks in advance.

/Steen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 07:47:09

select user.id,user.name,user.age,visits.Lastvisit from user inner join
(
SELECT userID, MAX(LastVisit) AS LastVisit FROM Visits GROUP BY UserID
) as visits
on user.userid=visits.userid

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 07:47:44

if sql 2000

SELECT u.ID,u.Name,u.Age, (SELECT TOP 1 LastVisit FROM VISITS WHERE UserID=u.ID ORDER BY LastVisit DESC)
FROM Users u

if sql 2005


SELECT u.ID,u.Name,u.Age, v.LastVisit
FROM Users u
CROSS APPLY (SELECT TOP 1 LastVisit FROM VISITS WHERE UserID=u.ID  ORDER BY LastVisit DESC)v

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 07:49:36

quote:
Originally posted by visakh16

if sql 2000

SELECT u.ID,u.Name,u.Age, (SELECT TOP 1 LastVisit FROM VISITS WHERE UserID=u.ID ORDER BY LastVisit DESC)
FROM Users u

if sql 2005

SELECT u.ID,u.Name,u.Age, v.LastVisit
FROM Users u
CROSS APPLY (SELECT TOP 1 LastVisit FROM VISITS WHERE UserID=u.ID ORDER BY LastVisit DESC)v

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

Your method for version 2000 may be ineffecient based on the volume of data

Madhivanan

Failing to plan is Planning to fail

SteenPedersen
Starting Member

2 Posts

Posted - 2010-02-12 : 07:55:32

Hi madhivanan
You're my hero of the day.
Thank you V-E-R-Y much. It's working (but of course: You know that already)!!

/Steen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 07:55:35

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

if sql 2000

SELECT u.ID,u.Name,u.Age, (SELECT TOP 1 LastVisit FROM VISITS WHERE UserID=u.ID ORDER BY LastVisit DESC)
FROM Users u

if sql 2005

SELECT u.ID,u.Name,u.Age, v.LastVisit
FROM Users u
CROSS APPLY (SELECT TOP 1 LastVisit FROM VISITS WHERE UserID=u.ID ORDER BY LastVisit DESC)v

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

Your method for version 2000 may be ineffecient based on the volume of data

Madhivanan

Failing to plan is Planning to fail

I just edited from a previous soultion

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-12 : 07:56:14

Both Madhi's INNER JOIN and VISAKH's CROSS APPLY will not return the user Donald.
So maybe there is a better use of LEFT JOIN or OUTER APPLY to show Donald without a LastVisit?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 07:58:15

oops missed that:-


SELECT u.ID,u.Name,u.Age, v.LastVisit
FROM Users u
OUTER APPLY (SELECT TOP 1 LastVisit FROM VISITS WHERE UserID=u.ID  ORDER BY LastVisit DESC)v

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 08:28:50

quote:
Originally posted by SteenPedersen

Hi madhivanan
You're my hero of the day.
Thank you V-E-R-Y much. It's working (but of course: You know that already)!!

/Steen

Thanks and you are welcome

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 08:31:25

quote:
Originally posted by webfred

Both Madhi's INNER JOIN and VISAKH's CROSS APPLY will not return the user Donald.
So maybe there is a better use of LEFT JOIN or OUTER APPLY to show Donald without a LastVisit?

No, you're never too old to Yak'n'Roll if you're too young to die.

I think the requrement is to show Last visit date for those who actually visited. Dont care about those who didn't

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-12 : 08:35:29

OP:

quote:
Based on the two tables above, I expect a result like this:

ID Name Age LastVisit
1 Jonn 45 31-01-2010
2 Jane 43 11-02-2010
3 Donald 57 NULL
4 Mickey 56 01-02-2010

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:36:46

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

Both Madhi's INNER JOIN and VISAKH's CROSS APPLY will not return the user Donald.
So maybe there is a better use of LEFT JOIN or OUTER APPLY to show Donald without a LastVisit?

No, you're never too old to Yak'n'Roll if you're too young to die.

I think the requrement is to show Last visit date for those who actually visited. Dont care about those who didn't

Madhivanan

Failing to plan is Planning to fail

I was also mislead by sentence.
For every user in the USER table, I just want to show the newest record from the VISITS table.
didnt bother to look closely at output

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 08:36:57

quote:
Originally posted by webfred

OP:
quote:
Based on the two tables above, I expect a result like this:

ID Name Age LastVisit
1 Jonn 45 31-01-2010
2 Jane 43 11-02-2010
3 Donald 57 NULL
4 Mickey 56 01-02-2010

No, you're never too old to Yak'n'Roll if you're too young to die.

I forgot to note that. Thanks. LEFT JOIN would do it

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-12 : 08:53:30

No problem.
Webfred is watching you!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources