Get the nearest value to the maximum value

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)

Get the nearest value to the maximum value

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-05 : 01:16:53

Hi guys,
The data is like this

5 2007-11-30
8 2007-12-02
12 2007-12-05

I need to get value 8 & 2007-12-02 because it is the nearest value to the maximum value i.e 12.
Any ideas...?

Cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 02:59:20

[code]-- Prepare sample data
declare @t table
(
ID int,
[Date] datetime
)

insert @t
select 5, '20071130' union all
select 8, '20071202' union all
select 12, '20071205'

-- Final Query
Select * from @t t1
where 1 = (select count(distinct t2.ID) from @t t2 where t2.ID > t1.ID)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 03:05:00

or

select top 1 * from @t t1
where id<(select max(id) from @t)
order by id desc

Select * from @t t1
where id=(select min(id) from (select top 2 id from @t order by id desc) as t)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources