Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL ORDER BY two columns, whichever is higher

Tags:

sql

mysql

I have a table that looks like so

ITEM_NAME     | NUM_SOLD | NUM_VIEWS
Apple         |       50 |        75
Orange        |       40 |        85
Pear          |       80 |        70
Cherry        |       15 |        60

I want to sort this by whichever number is highest in either of the last two columns.

So the above table would be sorted like so:

ITEM_NAME     | NUM_SOLD | NUM_VIEWS
Orange        |       40 |        85
Pear          |       80 |        70
Apple         |       50 |        75
Cherry        |       15 |        60

You can see that by the numbers I punched in, they're mostly sorted by NUM_VIEWS, but because Pear has a higher NUM_SOLD value than either of Apple's values, it gets sorted between Orange and Apple.

This Apple and Pear example is what I can't get to work properly.

like image 799
Delorean Avatar asked Aug 27 '12 19:08

Delorean


1 Answers

ORDER BY GREATEST(num_sold, num_views)

(See GREATEST(value1,value2,...) in §12.3.2 "Comparison Functions and Operators" of the MySQL 5.6 Reference Manual.)

like image 130
ruakh Avatar answered Nov 15 '22 07:11

ruakh