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.
CREATE TABLE TestTable (ID INT, Col1 VARCHAR(100));
INSERT INTO TestTable (ID, Col1)
SELECT 1, 'Positive'
SELECT 10, 'Positive'
SELECT 20, 'Positive'
SELECT -5, 'Negative'
SELECT -7, 'Negative'
SELECT -11, 'Negative';
-- Regular Select
-- Option 1
ORDER BY ABS(8 - ID) ASC;
-- Option 2
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)