How to take a number which compare nearby some numbers ?

Blue_Boy

Well,I'm gonna ask you a question. Case is: 1,29,30,32 and you want search the nearest number of 30? So, the nearest number is 29 and not 32, right? What if you have case like this : 1,29,30,31? Which nearest number you need to get? I think you have to write a function.

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

Lost User

Try something like this:

DECLARE @Input int
SET @Input = 30
SELECT TOP 1 Col1 FROM Table1
WHERE Col1 = (SELECT MIN(ABS(Col1-@Input))+@Input FROM Table1)
OR Col1 = (SELECT @Input-MIN(ABS(Col1-@Input)) FROM Table1)

You may need to modify this query.

Luc Pattyn

you can use the least-square approach, provided your numbers won't overflow when squared. Example:

SELECT * FROM news1 ORDER BY (news_id-10)*(news_id-10)

would order the news items according to their distance of news_id value 10. Then optionally pick the first one, or first few, with whatever your SQL environment needs for doing that (often "TOP 1"). :)

Luc Pattyn [Forum Guidelines] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Lost User

Awesome answer. Thanks for teaching me something new.:thumbsup:

Niladri_Biswas

This will work gracefully declare @t table(num int) insert into @t select 12 union all select 43 union all select 31 union all select 35 declare @inputNum int set @inputNum = 30

select num as [Closest Number] from(
select DENSE_RANK() over(order by abs(num - @inputNum)) [rank]
,num from @t
)X
where [rank] = 1

Output:

Closest Number
31

Note: Try with Input Output 100 43 -1 12 39 43 & 35 (which is correct only because the difference is 4 for both the case). Henceforth I have used Dense_Rank() Hope this helps :)

Niladri Biswas

modified on Monday, November 23, 2009 9:37 AM

Lost User

Better yet, this query works too:

SELECT * FROM news1 ORDER BY ABS(news_id-10)

Luc Pattyn

of course it does, however using squares is a common technique to optimize a "cost function", say when you need to find a best match for several parameters, you do:

"... ORDERBY (var1-goal1)*(var1-goal1)+(var2-goal2)*(var2-goal2)...+(varN-goalN)*(varN-goalN)"

:)

Luc Pattyn [Forum Guidelines] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Lost User

Here is a better answer:

DECLARE @i int

SET @i = 30

SELECT TOP 1 Col1 FROM Table1 ORDER BY ABS(Col1-@i)

Niladri_Biswas

Hi, Just 1 question to ask? If the input is 39 what be the output? Is it 43 or 35 or both? If the answer is the last one, then how

Shameel wrote:

TOP 1 Col1

will help? :)

Niladri Biswas

Golden Jing · modified on Monday, November 23, 2009 9:37 AM

thanks you so much Niladri Diswas....

VB.Net

Golden Jing

Dear All Thanks for you help. Now i can get that task ready. Best regards,

VB.Net

Lost User

If you remove the 'TOP 1' clause, you will see that the query returns both values. Adding TOP clause arbitrarily returns the first match.

Roger Wright

I believe the above will order the items by the value of news_id-10, not by their distance from the target value of 30. To do that, you would have to change the ORDER BY clause to ((news_id-10 * new_id-10) - (target_value * target_value)). It might be easier still to use ABS(ABS(news_id-10) - ABS(target_value)) though using the function ABS() may slow execution somewhat. I've been trying to figure out a way to use MIN(ABS(news_id-10) - ABS(target_value)) in a way that will return a record instead of a single value, but I lack the knowledge for that (for now). That would give him the desired information in one step.

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Luc Pattyn

Hi Roger, I don't know what you are trying with all those ABS functions, they don't make sense to me. My point is: to order items according to their distance from a target or goal, you either use a single ABS (useful only for one criterium, one goal), or a least-square approach (where you calculate a formula involving squaring deviations as I indicated before). My example with news_id (and target 10) wasn't an exact match for the OP, it was the code that I tested for something of mine. :)

Luc Pattyn [Forum Guidelines] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Roger Wright

I thought the ABS function might be a shortcut, in that it accomplishes the same thing as squaring when you want only the distance between two values, regardless of which is smaller. My main consideration was that the example you gave did not return a distance from the target of 30, only reorders the table. Now that I reread your response, I see that the value I was reading as a single variable (news_id-10) is actually (new_id - 10). That's what I get for reading this stuff late at night after a long day at work, wiring sewer plant pump motors in a frigid dust storm. My bad. :-O

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Luc Pattyn

NP :)

Luc Pattyn [Forum Guidelines] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages