CODEWITHSUNDEEP
phpsqlsql-order-by
kangoroo
kangoroo

Reputation: 359

SQL ORDER BY an AS variable

I have an sql query sorta like this: 

SELECT

.....
price

..... AS distance

ORDER BY case
  ...

my question is, how can i use the calculated distance as a sorting element?

Upvotes: 0

Views: 132

Answers (2)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

It's unclear for me what "distance" is in the question's query and why you put case in oeder by.

In general, you can mention field within ORDER BY clause either by its alias or by its number within the select clause. You can put some logic into ORDER BY as well

  select A,
         B as MyB,
         C - Z, -- <- Field #3: (A - #1, B - #2, C - Z - #3, D + F + G - #4 etc)
         D + F + G
    from MyTable
order by MyB asc,       -- <- by alias
         3 desc,        -- <- by field's number
         D + F + G asc, -- <- by some logic, here it's just copy + paste
         case           -- <- by some logic designed for ORDER BY only
           when (A > B + C) then
             1
           when (A > B)  then
             2
           else    
             3
         end desc

Upvotes: 1

davek
davek

Reputation: 22905

either use the same definition for distance in the order by clause:

select 
...
price
<some logic here> as distance
...
order by <same logic here>

or refer to the derived column from an outer select:

select * from
(
select
...
price
... as distance
...
) x
order by x.distance

Upvotes: 0

Related Questions