first record in sql server

chickanna
chickanna used Ask the Experts™
on
experts... I have a sybase query like this.

      select
            first @RecordStatusID = lm.RecordStatusID
      From
            it_systems.ft_ArchivedLoanBox alb
      left join
            it_systems.ft_ArchivedLoan al
      on
            (alb.ArchiveID = al.ArchiveID)
      left join
            ezadmin.ft_LoanMaster lm
      on
            (al.LoanID = lm.LoanID)
      where
            (alb.ArchiveBoxNum) = (@ArchiveBoxNum)


What is the equivalent function for "first" in SQL Server 2005/2008??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
use top 1 in SQL Server

select top1 from .....

Commented:
It should be

select top 1 * from yourtable ....
Commented:
Query should be


select
            TOP 1 @RecordStatusID = lm.RecordStatusID
      From
            it_systems.ft_ArchivedLoanBox alb
      left join
            it_systems.ft_ArchivedLoan al
      on
            (alb.ArchiveID = al.ArchiveID)
      left join
            ezadmin.ft_LoanMaster lm
      on
            (al.LoanID = lm.LoanID)
      where
            (alb.ArchiveBoxNum) = (@ArchiveBoxNum)

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Minor technical note (NO pts of course :) ):

Technically you should enclose the "1" in parentheses, like so:

SELECT TOP (1) ...
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
IIRC, that is the newer, and preferred, syntax.
But top won't necessarily get yo uthe same record every time, unless you specify an absolute WHERE clause, or use and "order by" statement.  Based on the fact that you need top, I'm assuming there where clause won't just return 1 record, so figure out the proper order by and add it.  Otherwise, without the order by, there is no guarantee of the order.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial