Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. How to take a number which compare nearby some numbers ?

How to take a number which compare nearby some numbers ?

Scheduled Pinned Locked Moved Database
tutorialcsharpdatabasesql-server
19 Posts 6 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • G Golden Jing

    Dear All, I need your help and deal. I'm using SQL Server 2005. I want to select a number that nearby a number that i want. Example: 30 is number i need. when select from table have some number equal it. So i want a number is so near 30. (12, 43, 31, 35) = 31 I am sorry if descipt not clear... Thanks you for your help. Best Regards, Sovann

    VB.Net

    N Offline
    N Offline
    Niladri_Biswas
    wrote on last edited by
    #8

    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

    G 1 Reply Last reply
    0
    • L 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


      L Offline
      L Offline
      Lost User
      wrote on last edited by
      #9

      Better yet, this query works too:

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

      L 1 Reply Last reply
      0
      • L Lost User

        Better yet, this query works too:

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

        L Offline
        L Offline
        Luc Pattyn
        wrote on last edited by
        #10

        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


        1 Reply Last reply
        0
        • G Golden Jing

          Dear All, I need your help and deal. I'm using SQL Server 2005. I want to select a number that nearby a number that i want. Example: 30 is number i need. when select from table have some number equal it. So i want a number is so near 30. (12, 43, 31, 35) = 31 I am sorry if descipt not clear... Thanks you for your help. Best Regards, Sovann

          VB.Net

          L Offline
          L Offline
          Lost User
          wrote on last edited by
          #11

          Here is a better answer:

          DECLARE @i int

          SET @i = 30

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

          N 1 Reply Last reply
          0
          • L Lost User

            Here is a better answer:

            DECLARE @i int

            SET @i = 30

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

            N Offline
            N Offline
            Niladri_Biswas
            wrote on last edited by
            #12

            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

            L 1 Reply Last reply
            0
            • N 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

              G Offline
              G Offline
              Golden Jing
              wrote on last edited by
              #13

              thanks you so much Niladri Diswas....

              VB.Net

              1 Reply Last reply
              0
              • G Golden Jing

                Dear All, I need your help and deal. I'm using SQL Server 2005. I want to select a number that nearby a number that i want. Example: 30 is number i need. when select from table have some number equal it. So i want a number is so near 30. (12, 43, 31, 35) = 31 I am sorry if descipt not clear... Thanks you for your help. Best Regards, Sovann

                VB.Net

                G Offline
                G Offline
                Golden Jing
                wrote on last edited by
                #14

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

                VB.Net

                1 Reply Last reply
                0
                • N 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

                  L Offline
                  L Offline
                  Lost User
                  wrote on last edited by
                  #15

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

                  1 Reply Last reply
                  0
                  • L 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


                    R Offline
                    R Offline
                    Roger Wright
                    wrote on last edited by
                    #16

                    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"

                    L 1 Reply Last reply
                    0
                    • R 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"

                      L Offline
                      L Offline
                      Luc Pattyn
                      wrote on last edited by
                      #17

                      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


                      R 1 Reply Last reply
                      0
                      • L 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


                        R Offline
                        R Offline
                        Roger Wright
                        wrote on last edited by
                        #18

                        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"

                        L 1 Reply Last reply
                        0
                        • R 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"

                          L Offline
                          L Offline
                          Luc Pattyn
                          wrote on last edited by
                          #19

                          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


                          1 Reply Last reply
                          0
                          Reply
                          • Reply as topic
                          Log in to reply
                          • Oldest to Newest
                          • Newest to Oldest
                          • Most Votes


                          • Login

                          • Don't have an account? Register

                          • Login or register to search.
                          • First post
                            Last post
                          0
                          • Categories
                          • Recent
                          • Tags
                          • Popular
                          • World
                          • Users
                          • Groups