CODEWITHSUNDEEP
phpmysqlsql-order-bymax
Dan Stern
Dan Stern

Reputation: 2167

ordering mysql table for the max of two variables

hope someone can help me with this issue.

I have a table with two columns, and i want to select items from that table and order them depending on which of these two values is higher.

lets say i have columns 'x' and 'y' and i have these entries:

     x ;  y
1.- 10 ; 12
2.-  5 ; 10
3.- 11 ; 20

i want the response to be ordered: 3, 1, 2

i know this wont help, since it has no sence in the mysql, but is a representation of my idea

$query = (mysql_query("SELECT * FROM productos ORDER BY MAX(x, y)"));

i dont want to create a new variable in the table for this query.

Any idea? thanks a lot

Upvotes: 2

Views: 2428

Answers (3)

ZoolWay
ZoolWay

Reputation: 5505

You want to sort it after x or y, depending which is higher?

SELECT * FROM products ORDER BY GREATEST(x, y)

But in your case this is 3, 2, 1 because 15(y) is higher than -5(x) and also higher than the greatest in row 1. So I am a bit confused.

Supporting NULLs and correct order:

SELECT * FROM products ORDER BY GREATEST(COALESCE(x, 0), COALESCE(y, 0)) DESC

If you need other value as default than 0 just replace it.

Upvotes: 6

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

SELECT * 
FROM products 
ORDER BY GREATEST(x,y) DESC
       , LEAST(x,y) DESC

The second part of the ordering (LEAST) is to put a row with (12,20) before a row with (10,20).

Since the two fields can have NULL:

SELECT * 
FROM products 
ORDER BY GREATEST( COALESCE(x,0), COALESCE(y,0) ) DESC
       , LEAST(    COALESCE(x,0), COALESCE(y,0) ) DESC ;

Upvotes: 2

CResults
CResults

Reputation: 5105

Select 
  * 
from 
  products
order by
  Case when x>y then x else y end DESC

Upvotes: 2

Related Questions