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.

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.

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

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.

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

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

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

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 (http://blog.sqlauthority.com)

5 thoughts on “SQL SERVER – MySQL – Order Column by Nearest Value for Any Integer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s