How to Select Top 2 Rows? - 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)

How to Select Top 2 Rows?

Author

Topic

anishap
Yak Posting Veteran

61 Posts

Posted - 2013-05-31 : 15:51:33

Hello,

Please see the below query. I want to pull only the top 2 rows from the TUBER table but all rows from EXPOSURE table. Each employee will have many TB.FLDDATE, right now it's showing everything.

Do you have any insight on how to do this on the below query?

-----------------------------------------------------------

SELECT H.FLDEXPOSURE,EX.FLDDESCR,E.FLDID,E.FLDLNAME, E.FLDFNAME, E.FLDADD1, E.FLDCITY,E.FLDSTATE,E.FLDMAILSTOP,E.FLDEMAIL,E.FLDHOMEPHON,
TBLAST = CONVERT(VARCHAR(10),TB.FLDDATE,101),
TB.FLDMMREACT,
TBSS = CONVERT(VARCHAR(10),TBSS.FLDDATE,101)

FROM EMPLOYEE E INNER JOIN EXPHIST H ON E.FLDREC_NUM = H.FLDEMPLOYEE
INNER JOIN EXPOSURE EX ON EX.FLDCODE = H.FLDEXPOSURE

LEFT OUTER JOIN TUBER TB
ON TB.FLDEMPLOYEE = E.FLDREC_NUM
AND TB.FLDDATE <= getdate()

LEFT OUTER JOIN PHYSLOG TBSS
ON TBSS.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSS.FLDDATE <= getdate() AND TBSS.FLDTYPE = 'TBSS'

WHERE
E.FLDCOMP = @COMP AND
H.FLDEXPOSURE = @EXPOSURE

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-31 : 17:13:29

Make the join on TUBER into a subquery

SELECT  H.FLDEXPOSURE ,
        EX.FLDDESCR ,
        E.FLDID ,
        E.FLDLNAME ,
        E.FLDFNAME ,
        E.FLDADD1 ,
        E.FLDCITY ,
        E.FLDSTATE ,
        E.FLDMAILSTOP ,
        E.FLDEMAIL ,
        E.FLDHOMEPHON ,
        TBLAST = CONVERT(VARCHAR(10), TB.FLDDATE, 101) ,
        TB.FLDMMREACT ,
        TBSS = CONVERT(VARCHAR(10), TBSS.FLDDATE, 101)
FROM    EMPLOYEE E
        INNER JOIN EXPHIST H ON E.FLDREC_NUM = H.FLDEMPLOYEE
        INNER JOIN EXPOSURE EX ON EX.FLDCODE = H.FLDEXPOSURE
        LEFT OUTER JOIN 
        (SELECT TOP (2) * FROM TUBER TB WHERE TB.FLDEMPLOYEE = E.FLDREC_NUM
			AND TB.FLDDATE <= GETDATE() ORDER BY TB.FLDDATE DESC ) AS  TB
		ON TB.FLDEMPLOYEE = E.FLDREC_NUM
                                    AND TB.FLDDATE <= GETDATE()
        LEFT OUTER JOIN PHYSLOG TBSS ON TBSS.FLDEMPLOYEE = E.FLDREC_NUM
                                        AND TBSS.FLDDATE <= GETDATE()
                                        AND TBSS.FLDTYPE = 'TBSS'
WHERE   E.FLDCOMP = @COMP
        AND H.FLDEXPOSURE = @EXPOSURE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 05:42:40

should be OUTER APPLY i guess than LEFT JOIN if you want to have correlated query based on passed FLDREC_NUM value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

anishap
Yak Posting Veteran

61 Posts

Posted - 2013-06-03 : 10:57:54

I tried this before but I'm getting an error message E.FLDREC_NUM could not be bound.

thanks for the repsonse.

quote:

Originally posted by James K

Make the join on TUBER into a subquery

SELECT  H.FLDEXPOSURE ,
        EX.FLDDESCR ,
        E.FLDID ,
        E.FLDLNAME ,
        E.FLDFNAME ,
        E.FLDADD1 ,
        E.FLDCITY ,
        E.FLDSTATE ,
        E.FLDMAILSTOP ,
        E.FLDEMAIL ,
        E.FLDHOMEPHON ,
        TBLAST = CONVERT(VARCHAR(10), TB.FLDDATE, 101) ,
        TB.FLDMMREACT ,
        TBSS = CONVERT(VARCHAR(10), TBSS.FLDDATE, 101)
FROM    EMPLOYEE E
        INNER JOIN EXPHIST H ON E.FLDREC_NUM = H.FLDEMPLOYEE
        INNER JOIN EXPOSURE EX ON EX.FLDCODE = H.FLDEXPOSURE
        LEFT OUTER JOIN 
        (SELECT TOP (2) * FROM TUBER TB WHERE TB.FLDEMPLOYEE = E.FLDREC_NUM
			AND TB.FLDDATE <= GETDATE() ORDER BY TB.FLDDATE DESC ) AS  TB
		ON TB.FLDEMPLOYEE = E.FLDREC_NUM
                                    AND TB.FLDDATE <= GETDATE()
        LEFT OUTER JOIN PHYSLOG TBSS ON TBSS.FLDEMPLOYEE = E.FLDREC_NUM
                                        AND TBSS.FLDDATE <= GETDATE()
                                        AND TBSS.FLDTYPE = 'TBSS'
WHERE   E.FLDCOMP = @COMP
        AND H.FLDEXPOSURE = @EXPOSURE

anishap
Yak Posting Veteran

61 Posts

Posted - 2013-06-03 : 11:10:57

I tried OUTER APPLY and I think it's working

OUTER APPLY
(SELECT TOP (2) * FROM TUBER TB WHERE TB.FLDEMPLOYEE = E.FLDREC_NUM
AND TB.FLDDATE <= GETDATE() ORDER BY TB.FLDDATE DESC ) AS TB

thanks for your response

quote:
Originally posted by visakh16

should be OUTER APPLY i guess than LEFT JOIN if you want to have correlated query based on passed FLDREC_NUM value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 00:04:00

quote:
Originally posted by anishap

I tried OUTER APPLY and I think it's working

OUTER APPLY
(SELECT TOP (2) * FROM TUBER TB WHERE TB.FLDEMPLOYEE = E.FLDREC_NUM
AND TB.FLDDATE <= GETDATE() ORDER BY TB.FLDDATE DESC ) AS TB

thanks for your response

quote:
Originally posted by visakh16

should be OUTER APPLY i guess than LEFT JOIN if you want to have correlated query based on passed FLDREC_NUM value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources