CODEWITHSUNDEEP
sqlpostgresql
Tom Carrick
Tom Carrick

Reputation: 6616

SQL order by highest value of two columns

I have a PostgreSQL table with some doubles, they store precentages. so let's say the columns are pc_1 and pc_2.

What I want is to order by whichever of these two columns has the highest amount descending, and then by the other column, again descending.

So if our data is as follows:

id  pc_1  pc_2 
 1  12.5  11.0
 2  10.0  13.2
 3  13.2  9.0

select * from mytable order by <something>

Would give:

 2  10.0  13.2
 3  13.2  9.0
 1  12.5  11.0

Upvotes: 12

Views: 6571

Answers (2)

user610217
user610217

Reputation: 

select *
from mytable
order by 
case when pc_1 > pc_2 then pc_1 else pc_2 end desc,
case when pc_1 > pc_2 then pc_2 else pc_1 end desc

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425261

SELECT  *
FROM    mytable
ORDER BY
        GREATEST(pc_1, pc_2) DESC, LEAST(pc_1, pc_2) DESC

Upvotes: 24

Related Questions