How do I get the first record in a where clause.

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 2000 Forums

Transact-SQL (2000)

How do I get the first record in a where clause.

Author

Topic

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-28 : 16:09:33

I need to have the first record that is being returned and store it in in a variable.
This doesn't work:


declare @clientid smallint 
select clientid  = top 1 clientid from clientlist  where clientid > 200

So I would need 201.
Is there a way ?

robert

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-28 : 16:13:23

I found it:


set @clientid = (select  top 1 clientid from clientlist where clientid > 200)

robert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 03:26:09

What is wrong with first method?

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-29 : 06:08:25

Don't forget that you need an order by with the top operator. You can do it without the top operator too, but the execution plans are the same...

--data
declare @clientlist table (clientid int)
insert @clientlist
          select 343
union all select 232
union all select 201

declare @clientid smallint 

--calculation (1)
set @clientid = (select top 1 clientid from @clientlist where clientid > 200 order by clientid)
select @clientid

--calculation (2)
select @clientid = clientid from @clientlist where clientid > 200 order by clientid desc
select @clientid

/*results
------ 
201
       
------ 
201
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources