select Nerest value using SQL - 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 2000 Forums

SQL Server Development (2000)

select Nerest value using SQL

Author

Topic

kulkarni_ajit
Starting Member

4 Posts

Posted - 2009-03-11 : 07:04:26

Hi

I have table called LTDriveSelectionNew

with Field

ref Voltage

ref1 0.1
ref2 0.2
ref3 0.3
ref4 0.4
ref5 0.5
...... and so on

i want to select vaules Nerest value

for Eg.

Select Ref,Voltage from LTDriveSelectionNew
where voltage= 0.31
then it should display

nearest Min values

ie Ref2 0.3

please reply me it is urgent

waiting for your reply
regards
ajit

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-03-11 : 07:19:16

[code]-- *** Test Data ***
DECLARE @t TABLE
(
Ref char(4) NOT NULL
,Voltage decimal(5,1) NOT NULL
)
INSERT INTO @t
SELECT 'ref1', 0.1 UNION ALL
SELECT 'ref2', 0.2 UNION ALL
SELECT 'ref3', 0.3 UNION ALL
SELECT 'ref4', 0.4 UNION ALL
SELECT 'ref5', 0.5
-- *** End Test Data ***

SELECT T.Ref, T.Voltage
FROM @t T
WHERE T.Voltage =
(
SELECT MAX(T1.Voltage)
FROM @t T1
WHERE T1.Voltage <= 0.31
)
[/code]