nested 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

SQL Server 2008 Forums

Transact-SQL (2008)

nested query

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-06-24 : 11:47:07

i have the following nested query in my main query.
It retrieves the min authored date of a document, and i would like to get the associated author. (displayname)
when i run the query i get all the authors for the documents and the corresponding dates. in order to get the min authored date and the correct author i think i would have to create a query inside this one to get my displayname.
not sure how to do that. i think i would have to join the sub query to this query by min authored date to the authored date????

SELECT distinct
min(cd.AuthoredDtm) AS INIT_NRS_ASSES,
Displayname AS NRS_ROLE_NAME,
ClientVisitGUID

FROM
dbo.CV3ClientDocument cd
INNER JOIN
dbo.CV3User ON cd.UserGUID = dbo.CV3User.GUID
where orderroletype ='Nurse' and entrytype='4'

group by ClientVisitGUID,Displayname

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-24 : 12:24:43

Would a subquery like this work for you?


SELECT
   INIT_NRS_ASSES,NRS_ROLE_NAME,ClientVisitGUID
FROM
(
   SELECT
      ROW_NUMBER() OVER (PARTITION BY ClientVisitGUID ORDER BY cd.AuthoredDtm) AS RN,
      cd.AuthoredDtm AS INIT_NRS_ASSES,
      Displayname AS NRS_ROLE_NAME,
      ClientVisitGUID
   FROM
      dbo.CV3ClientDocument cd
      INNER JOIN dbo.CV3User
         ON  cd.UserGUID = dbo.CV3User.GUID
   WHERE
      orderroletype = 'Nurse'
      AND entrytype = '4'
) N
WHERE RN = 1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources