SQL SERVER – MySQL – Order Column by Nearest Value for Any Integer

Here is a very interesting question I received, which is based on my MySQL courses. The same script works on SQL Server as well as MySQL. Let us understand how to Order Column by Nearest Value for Any Integer.

Here is the question:

“I have a table containing a column with positive and negative integer. I want to order the column based on the nearest or closest proximity of the value. For example, I might want to order the column in such a way that it is ordered based on the distance from any integer.”

For example, here is my original result set.

Now let us assume that I want to order this table based on the nearest value to -5 the result will be changed to following orders.

Now if I want to order this table based on the nearest value of the +8 the result will now change into following orders.

Any idea how I can do that?”

Very interesting question, actually this can be done with the help of following script where I have used ABS.

USE test;
CREATE TABLE TestTable (ID INT, Col1 VARCHAR(100));
INSERT INTO TestTable (ID, Col1)
SELECT 1, 'Positive'
UNION ALL
SELECT 10, 'Positive'
UNION ALL
SELECT 20, 'Positive'
UNION ALL
SELECT -5, 'Negative'
UNION ALL
SELECT -7, 'Negative'
UNION ALL
SELECT -11, 'Negative';
-- Regular Select
SELECT *
FROM TestTable;
-- Option 1
SELECT *
FROM TestTable
ORDER BY ABS(8 - ID) ASC;
-- Option 2
SELECT *
FROM TestTable
ORDER BY ABS(-5 - ID) ASC;
-- Drop table
DROP TABLE TestTable;

Well, I think this should do it. If you reverse the order of the ID and another integer, it will still give you the same result. Any other idea?

Reference: Pinal Dave (https://blog.sqlauthority.com)