Ordering based on 4 values

[mrjts]

I am working on a latitude/longitude distance search and am having trouble finding the closest results.

The issue is that I do 4 calculations and if I "ORDER BY", I get them sorted by first, then second, then third, then fourth value, as to be expected. What I need to do is find the smallest value of the 4 calculations first and then I can order by that one value.

Problem is, I am at beginner level SQL statements.

Here is the problem in more simplified terms:
a = 40
b = 10
c = 50
d = 20

I need to find the smallest value of the following to use as the value to sort my results:
a - column_1
,column_1 - b
,c - column_2
,column_2 - d

 

[martynh]

A bit yukky, but if you write 4 separate queries (one per calculation) and union them together, then order by the calculated column you will get the correct answer. You might find that adding an extra column, of a literal, helps. This way you can identify which calculation produced the lowest value:

select a - column_1 as calc, 'a' as id ....
union
select column_1 - b as calc, 'b' as id ....
union
select c - column_2 as calc, 'c' as id ....
union
select column_2 - d as calc, 'b' as id ....
order by 1 desc