1. ## 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. Code:
```SELECT value
FROM daTable
WHERE ABS(value-50) =
( SELECT MIN(ABS(value-50)) FROM daTable )```

3. 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. 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. Originally Posted by poizn
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 )
;```

6. 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. 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

8. 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. how many locations are there, then? bazillions? millions? thousands?

a table of numbers isn't all that hard to create...

10. 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. 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

12. 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. that's correct

do a search in the Program Your Site forums (include child forums php, databases, mysql) for integers table

14. Interesting, thank you

15. 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. 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. I'm trying to find the nearest empty location, so that something can be put on it, that's all.

18. Originally Posted by Antnee
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

19. 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. Originally Posted by Antnee
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.

21. 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 )```

22. 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?

23. 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. Originally Posted by Antnee
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. Thought about it, suggested it, and eventually decided I don't like getting kicked in the nuts so I stopped suggested it

#### Posting Permissions

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