SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Select Nearest Value

    Guys,

    I need to find a value in the database that is the nearest to a given value. It can be greater than, less than or equal to.

    So, for example:

    Values: 40, 42, 45, 48, 51, 53, 55, 58

    Select the nearest value to 50. Obviously in the above example it would be 51. However, with this series:

    Values: 40, 42, 45, 48, 53, 55, 58, 60

    Now, the nearest value is 48.

    I've not been able to find an obvious solution. I did consider simply doing something like:

    Code MySQL:
    SELECT * FROM table_name WHERE column_name >= 50 ORDER BY column_name ASC LIMIT 0,1

    and then

    Code MySQL:
    SELECT * FROM table_name WHERE column_name <= 50 ORDER BY column_name DESC LIMIT 0,1

    Using PHP I would then see which result has a smaller difference between the given value and the result. Not the most efficient way though, surely?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT value
      FROM daTable
     WHERE ABS(value-50) =
           ( SELECT MIN(ABS(value-50)) FROM daTable )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jun 2006
    Location
    Durban, South Africa
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It doesn't work is the "number you input" (ie the 50 in the above case) is greater than the max number in the table...

  4. #4
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937, thanks for that. That's new on me I must admit.

    poizn, was that to me or r937? What we're doing is defining a number of locations, in this example between 1 and 100. A workstation is in the middle, ie location 50 for simplicities sake. What we wanted to do was to work out where the nearest empty location is. Granted this only works with a 2D layout, but it's good enough for the purposes. Anyway, as a result, the value (50 in my case) could potentially be the highest number, but only until the first couple of locations have been filled (49 to 51 I guess).

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by poizn View Post
    It doesn't work is the "number you input" (ie the 50 in the above case) is greater than the max number in the table...
    yes it does
    Code:
    CREATE TABLE test_abs
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , foo INTEGER
    );
    INSERT INTO test_abs (foo) VALUES
     ( 2 ),( 4 ),( 6 ),( 9 ),( 3 ),( 7 )
    ;
    SELECT foo
      FROM test_abs
     WHERE ABS(foo-50) =
           ( SELECT MIN(ABS(foo-50)) 
               FROM test_abs ) 
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Now I'm trying to use this I'm not so sure it's right. I have two values in PHP as variables (also in a table called params). These two values define the upper and lower limits, numerically (INT) for the locations. I will only store the locations that have something on them. So I can't select a value that's actually in the table, rather I need to find the next one that isn't.

    So, I start at 50 and go down. I see that 49 has something in it, and when going up I find that 51 does too. 48 on the other hand is not found, and therefore is empty. 52 is found to have something in it. So I guess I would expect results to go something like:

    50,49,51,47,52

    That's assuming the result was returned in the order of where items are found in locations. I'd have to notice that 48 was skipped to realise it was empty, right?

    I'm starting to think that a PHP solution might be easier, and possibly more efficient too. Am I wrong?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    create a table of numbers from 1 to 100

    do a left outer join with the numbers already allocated, check for IS NULL, and this will give you all the "holes"

    then apply the formula above to find the nearest hole instead of the nearest filled location
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I see. I didn't think it made for a very flexible system if I was creating a table with all the locations listed in it to be honest.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how many locations are there, then? bazillions? millions? thousands?

    a table of numbers isn't all that hard to create...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ha ha, you're right, it's not, it just seemed like pointless information when I was thinking about it originally. At the moment there is no fixed number, I'm just using 100 for the sake of testing and development. I gather there may be as many as 1400 to start with, and it will most likely grow at random intervals.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    create a table of 100 numbers, like this --
    Code:
    CREATE TABLE integers (i INTEGER NOT NULL PRIMARY KEY);
    INSERT INTO integers (i) VALUES 
     (0),(1),(2),(3),(4),(5),(6),(7),(8),(9), ... (99);
    now you can generate 10000 numbers with this simple SQL --
    Code:
    SELECT 100*x.i + y.i AS n
      FROM integers AS x
    CROSS
      JOIN integers AS y
    now you can LEFT OUTER JOIN from here to your data, joining on n --
    Code:
    SELECT 100*x.i + y.i AS location_hole
      FROM integers AS x
    CROSS
      JOIN integers AS y
    LEFT OUTER
      JOIN yourtable
        ON yourtable.location = 100*x.i + y.i 
     WHERE yourtable.location IS NULL
    this gives you the missing locations

    the table of numbers from 0 to 99 will come in handy in many queries, not just this one, so it's quite useful to have
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ah, so you're not saying that I need a list of numbers for this particular task, but rather one that I can use in different queries for different aspects? Interesting. Thanks

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's correct

    do a search in the Program Your Site forums (include child forums php, databases, mysql) for integers table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Interesting, thank you

  15. #15
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, I've created the integer table, and sure enough your query tells me that location 51 has something on it by omitting it from the list of available locations. It's still not quite what I'm looking for though, and I'm still at a loss as to how to achieve what I want, which is going back to my original question.

    I need to find the nearest empty location to the given (in this case 50). I tried to merge your original suggestion with this integer table method, but I don't think I understand what's going on enough to be able to do it

  16. #16
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Step back for a minute and think about and then tell us the problem you are really trying to solve. why do you need to find "empty" locations. Are you trying to fill in gaps in your table or something? If so why?

  17. #17
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to find the nearest empty location, so that something can be put on it, that's all.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    I tried to merge your original suggestion with this integer table method, but I don't think I understand what's going on enough to be able to do it
    could you show the query you tried? it's probably a small detail and it would be easier for me to edit yours rather than rewrite it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    SELECT x.i AS empty
      FROM integers AS x
    LEFT OUTER
      JOIN aplocate_data
        ON aplocate_data.location = x.i 
     WHERE aplocate_data.location IS NULL
     AND
         ABS(x.i - 50)
            =
        (SELECT MIN(ABS(x.i - 50)) FROM aplocate_data)

    I'm only working with 100 locations at this point, hence why I'm not using the CROSS JOIN now as the integer table has 100 in it

  20. #20
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Antnee View Post
    I'm trying to find the nearest empty location, so that something can be put on it, that's all.
    why do you need an empty location? why can't you just insert the record and not worry about if it is an empty location or not. It seems like a lot of work. You aren't using a flat file system but seem to be thinking of your table as such.

    I've seen many times that people use an auto_increment column type and then worry about filling in missing gaps so that their front end application (which is usually written incorrectly) can traverse from the current record to the previous and next record.

    It's why I asked.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT x.i AS empty
      FROM integers AS x
    LEFT OUTER
      JOIN aplocate_data
        ON aplocate_data.location = x.i 
     WHERE aplocate_data.location IS NULL
       AND ABS(x.i - 50) =
           ( SELECT MIN(ABS(y.i - 50)) 
               FROM integers AS y
             LEFT OUTER
               JOIN aplocate_data
                 ON aplocate_data.location = y.i 
              WHERE aplocate_data.location IS NULL )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Basically, we are storing items on predetermined locations. The workstation that manages it is in the middle. The boss wants to know where the nearest empty location is so that the operators are directed toward it, rather than hunting around for the nearest empty location, or walking to the far end of the room. Seriously, that's how far they're taking optimising man-hours in there

    r937, that query works now, but I have units on location 50 and 51, and it's telling me that the nearest empty location is 52, when it should be 49. Any ideas? I notice that when I change

    Code MySQL:
    SELECT MIN(ABS(y.i - 50))

    to

    Code MySQL:
    SELECT MAX(ABS(y.i - 50))

    it finds the lower result. So I assume I need to be comparing the results of both and finding how close the result is to 50, right?
    Last edited by Antnee; Jan 13, 2009 at 11:50.

  23. #23
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    How messy is this?

    Code MySQL:
    SELECT x.i AS empty
      FROM integers AS x
    LEFT OUTER
      JOIN aplocate_data
        ON aplocate_data.location = x.i 
     WHERE aplocate_data.location IS NULL
       AND ABS(x.i - 50) =
           ( SELECT MAX(ABS(y.i - 50)) 
               FROM integers AS y
             LEFT OUTER
               JOIN aplocate_data
                 ON aplocate_data.location = y.i 
              WHERE aplocate_data.location IS NULL )
       OR ABS(x.i - 50) =
           ( SELECT MIN(ABS(y.i - 50)) 
               FROM integers AS y
             LEFT OUTER
               JOIN aplocate_data
                 ON aplocate_data.location = y.i 
              WHERE aplocate_data.location IS NULL )

    That returns the two nearest locations either side, so 48 and 51 are coming out with the current occupied locations being 49 and 50. Changing them to 50 and 51 I get 49 and 52 as results. I could use it, no problems, but that doesn't look like the way to do it really. Is it?

  24. #24
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Antnee View Post
    Basically, we are storing items on predetermined locations. The workstation that manages it is in the middle. The boss wants to know where the nearest empty location is so that the operators are directed toward it, rather than hunting around for the nearest empty location, or walking to the far end of the room. Seriously, that's how far they're taking optimising man-hours in there
    okay that makes sense, I just see the other "problem" over and over again in database forums.

    What about having the boss stand on the desk and just point to the work stations? ;-)

  25. #25
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    702
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thought about it, suggested it, and eventually decided I don't like getting kicked in the nuts so I stopped suggested it


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •