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.

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

About these ads

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