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)
5 Comments. Leave new
Hi, Pinal thanks for this post. It works fine the way you told. m working over this will post result soon.
Hi, Pinal Sir, A warm thanks for this post.
Nice post! Could this also be done with geografic data like coordinates ?
select * from @tbl Order by ABS(V-5)DESC
For Reverse
SELECT *
FROM TestTable
ORDER BY ABS(V-5)DESC