How to compute the min and max of two numbers?

I don't see there is a function to compute min or max of two numbers
in a row. Essentially I want to select all the rows where the minimum
of column1 and column2 are bigger than the maximum of col3 and col4
(The following code won't work as I expected, if I am correct).

select * from a_table where min(col1, col2) >= max(col3,col4)

I could expand the above expression to just using primitive
comparisons. But it would not be very readable. I'm wondering if there
is a better way to do so in sql.

--
Regards,
Peng

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to compute the min and max of two numbers?

On Tue, 27 Apr 2010 09:08:29 Peng Yu wrote:
> I don't see there is a function to compute min or max of two numbers
> in a row. Essentially I want to select all the rows where the minimum
> of column1 and column2 are bigger than the maximum of col3 and col4
> (The following code won't work as I expected, if I am correct).
>
> select * from a_table where min(col1, col2) >= max(col3,col4)

I think what you are looking for is GREATEST and LEAST. See also
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators. html#function_least
and
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators. html#function_greatest

Best regards,
Jesper

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to compute the min and max of two numbers?

--------------000305030109060903030100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>I want to select all the rows where the minimum
>of column1 and column2 are bigger than the maximum of col3 and col4

.... WHERE Least(col1,col2) > Greatest(col3, col4) ...

PB
http://www.artfulsoftware.com

-----

Peng Yu wrote:
> I don't see there is a function to compute min or max of two numbers
> in a row. Essentially I want to select all the rows where the minimum
> of column1 and column2 are bigger than the maximum of col3 and col4
> (The following code won't work as I expected, if I am correct).
>
> select * from a_table where min(col1, col2) >= max(col3,col4)
>
> I could expand the above expression to just using primitive
> comparisons. But it would not be very readable. I'm wondering if there
> is a better way to do so in sql.
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.437 / Virus Database: 271.1.1/2836 - Release Date: 04/26/10 06:31:00
>
>

--------------000305030109060903030100--