December 8, 2008 at 8:53 am
I need to select the top record from a query and store two values from it into variables.
something like this:
DECLARE @MRN1 int
DECLARE @DrID int
SET @DrID, @MRN1 = (SELECT TOP 1 c.ID, MRN
FROM coOutsideDoctors c
INNER JOIN ptEncounter e
ON e.RefPhysID = c.ID
WHERE (c.Deleted IS NULL)
AND (e.MRN = '5079')
ORDER BY e.DateOfService DESC))
I know this is not the way to do it but cannot find out how to do it.
December 8, 2008 at 8:55 am
DECLARE @MRN1 int
DECLARE @DrID int
SELECT TOP 1 @DrID = c.ID, @MRN1 = MRN
FROM coOutsideDoctors c
INNER JOIN ptEncounter e
ON e.RefPhysID = c.ID
WHERE (c.Deleted IS NULL)
AND (e.MRN = '5079')
ORDER BY e.DateOfService DESC
select @DrID, @MRN1
December 8, 2008 at 9:00 am
MrBaseball34 (12/8/2008)
I need to select the top record from a query and store two values from it into variables.something like this:
DECLARE @MRN1 int
DECLARE @DrID int
SET @DrID, @MRN1 = (SELECT TOP 1 c.ID, MRN
FROM coOutsideDoctors c
INNER JOIN ptEncounter e
ON e.RefPhysID = c.ID
WHERE (c.Deleted IS NULL)
AND (e.MRN = '5079')
ORDER BY e.DateOfService DESC))
I know this is not the way to do it but cannot find out how to do it.
--can you try this: [not tested]
;WITH MyCTE as (SELECT TOP 1 c.ID, MRN
FROM coOutsideDoctors c
INNER JOIN ptEncounter e
ON e.RefPhysID = c.ID
WHERE (c.Deleted IS NULL)
AND (e.MRN = '5079')
ORDER BY e.DateOfService DESC))
SET @DrID = (SELECT ID FROM MyCTE)
SET @MRN1 = (SELECT MRN FROM MyCTE)
December 8, 2008 at 9:01 am
Had a part in this did Yoda? 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2008 at 11:50 am
Grammar lessons you need. Help you I cannot. Yes!
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 8, 2008 at 12:13 pm
are you guys just warming up for the Q-A sessions or something? Not sure what you mean exactly. One thing for certain is that your Monday started well.
December 8, 2008 at 3:18 pm
Sergei Zarembo (12/8/2008)
--can you try this: [not tested]
;WITH MyCTE as (SELECT TOP 1 c.ID, MRN
FROM coOutsideDoctors c
INNER JOIN ptEncounter e
ON e.RefPhysID = c.ID
WHERE (c.Deleted IS NULL)
AND (e.MRN = '5079')
ORDER BY e.DateOfService DESC))
SET @DrID = (SELECT ID FROM MyCTE)
SET @MRN1 = (SELECT MRN FROM MyCTE)
The ways of the CTE powerful and mysterious are, but work this will not. Valid the CTE is for only one statement.
;WITH MyCTE as (SELECT TOP 1 c.ID, MRN
FROM coOutsideDoctors c
INNER JOIN ptEncounter e
ON e.RefPhysID = c.ID
WHERE (c.Deleted IS NULL)
AND (e.MRN = '5079')
ORDER BY e.DateOfService DESC))
SELECT @DrID = ID, @MRN1 = MRN FROM MyCTE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply