Post reply

How to retrieve the nearest & highest value from a list

  • November 4, 2011 at 6:37 am

    #247285

    Hi,

    Suppose I have a table with values 8000, 6000, 4000, 2000, 1000

    I want retrieve the nearest & highest value for 4999 from the above list:

    SELECT top 1 * FROM AmountConfig ORDER BY ABS(Amount - 4999)

    The above query will get me the value 4000. But I need the value to be 6000 & not 4000. Even if i supply 4001 i need to retrieve 6000.

    How can I do this?

    Regards,

    Nithin

  • November 4, 2011 at 6:53 am

    #1403187

    Nithin

    Can it be as simple as this:

    SELECT TOP 1 * FROM MyTable

    WHERE MyColumn > 4000

    ORDER BY MyColumn ASC

    John

  • November 4, 2011 at 7:02 am

    #1403191

    select top 1 * from AmountConfig

    where Amount>4999

    order by Amount asc

    Try the above statement

  • November 4, 2011 at 7:33 am

    #1403210

    Thanks a loooooot!!!!!!! John & Sridhar. I was soooo stupid to not to implement your simple idea!!:-)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply