Find Closest Number - 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 2005 Forums

Transact-SQL (2005)

Find Closest Number

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-07 : 01:57:19

HI, I have an employee table with columns EmpNo, EmpName. The table contains 100 records.

The Data in my table is as below.

EmpNo EmpName

124 Denky
321 snew
348 klore
412 lamnis

Now the requirement is if i give empno as input to query it has to retrive the record. If record exists it will retrive, if record not exist it has retrive the nearby employee number.

Suppose if i give input to query as 321 it retrives the record directly. If i give input as 362, though the record is not found in table it has retrive the closest employee record. i.e 348

How to write query for this? can any one help me in this regard?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-07 : 02:09:26

if exists (....)
Begin
....
End
Else
Begin

select top 1 EmpNo from <table_name> order by abs(EmpNo -<emp_no>)
End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-07 : 02:41:20

Thank you.. It works perfect

quote:
Originally posted by senthil_nagore

if exists (....)
Begin
....
End
Else
Begin

select top 1 EmpNo from <table_name> order by abs(EmpNo -<emp_no>)
End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-07 : 07:12:47

Welcome :)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources