CODEWITHSUNDEEP
oracle-databaseplsql
user747291
user747291

Reputation: 821

Order by depending on 2 column values

I have a query which returns the output as below, 

SELECT DISTINCT  a.rev_date ,fruitname,
 fruit_id , primary_fruit_id
FROM fruits a, fruit_lookup s,fruit_reference r 
WHERE a.id = s.id(+) 
 and primary_fruit_id = r.fruit_id(+) 
AND (fruit_id = 24 or fruit_id = 0) 
ORDER BY fruit_id desc, a.rev_date desc

How to handle this such that I still get the desired output where when both the id and primary id are same show them first? any idea is appreciated. I tried doing union etc but not getting the desired o/p. The order by fruit_id desc is not right as this is not ordering in the correct manner.thanks

 NAME       FRUIT_ID    PRIMARY_FRUIT_ID
--------------------------------------
apple       24          24
orange      24          12   
pear        24           7
apple       24          24
kiwi        24           6
apple       24          24   
apple       24          24
melon       24           2  
grape        0          90
banana       0          45
carrot       0          30

Desired output 

NAME    FRUIT_ID    PRIMARY_FRUIT_ID
--------------------------------------
apple       24          24    
apple       24          24
apple       24          24    
apple       24          24
kiwi        24           6    
orange      24          12   
melon       24           2      
pear        24           7
grape        0          90
banana       0          45
carrot       0          30

Upvotes: 1

Views: 82

Answers (2)

TommCatt
TommCatt

Reputation: 5636

You want to generate a value just to sort on based on the condition you want. 

ORDER BY fruit_id desc,
         a.rev_date desc,
         case when fruit_id = primary_fruit_id then 0 else 1 end

You don't show the date field in your sample outputs, so I don't know how that date segments the output. I'm guessing that the date is more relevant than the "same key value" but you can place it wherever gives you the effect you want.

ORDER BY fruit_id desc,
         case when fruit_id = primary_fruit_id then 0 else 1 end,
         a.rev_date desc

ORDER BY case when fruit_id = primary_fruit_id then 0 else 1 end,
         fruit_id desc,
         a.rev_date desc

Upvotes: 2

Gergely Bacso
Gergely Bacso

Reputation: 14651

Try this:

ORDER BY fruit_id desc, abs(fruit_id-PRIMARY_FRUIT_ID) asc

Upvotes: 1

Related Questions