CODEWITHSUNDEEP
mysqlminimum
Peter
Peter

Reputation: 1330

How to Select Minimum from two rows?

How to SELECT a minimum from two rows? I have tried this:

SELECT `DATE`, `PRICE1`, `PRICE2`, MIN (`PRICE1`, `PRICE2`) AS MINIMUM_OF_P1P2
FROM `TABLE`

I would like to get a result like this

DATE        PRICE1  PRICE2  MINIMUM_OF_P1P2
-------------------------------------------
2013-07-08  5.96    5.79    X.XX             <-- 5.79 expected
2013-07-09  5.89    6.04    X.XX             <-- 5.89 expected
2013-07-10  6.03    5.97    X.XX             <-- 5.97 expected
2013-07-11  6.08    6.10    X.XX             <-- 6.08 expected
2013-07-12  6.13    5.97    X.XX             <-- 5.97 expected
2013-07-15  6.15    6.25    X.XX             <-- 6.15 expected
2013-07-16  6.33    6.44    X.XX             <-- 6.33 expected
2013-07-17  6.47    6.35    X.XX             <-- 6.35 expected

But i get an error: FUNCTION MIN does not exist.

Upvotes: 1

Views: 1925

Answers (2)

Nic Wortel
Nic Wortel

Reputation: 11423

First of all, you're actually using the wrong function. If you need the lowest of two provided values, use the LEAST() function.

The MIN() function is an aggregate function. It returns the lowest value of multiple records.

SELECT MIN(`PRICE1`) AS MIN_PRICE1
FROM `TABLE`

would return only a single record:

MIN_PRICE1
----------
5.89

because that's the lowest value for PRICE1 in your table.

But why the 'FUNCTION MIN does not exist' error?

However, that's not what's triggering the error here. The problem is that there's a whitespace between the function name and opening parenthesis:

MIN (`PRICE1`, `PRICE2`)

From the MySQL manual:

Note

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

In other words, the space between MIN and ( causes that MySQL doesn't recognize MIN correctly, and throws an error message.

Solution:

Simply remove the whitespace between the function name and the opening parenthesis (.

SELECT `DATE`, `PRICE1`, `PRICE2`, LEAST(`PRICE1`, `PRICE2`) AS MINIMUM_OF_P1P2
FROM `TABLE`

Alternative solution

Or, you could tell MySQL that you'd like to use a space between the function name and opening parenthesis, as described here.

If you are used to spaces in front of parentheses and you'd like the interpreter to make some additional effort before reporting failure, you can always use the sql mode to inform the server of your preferences. Either start the server with the option --sql-mode=IGNORE_SPACE, or change it run-time:

set sql_mode='IGNORE_SPACE';

# or if you have the SUPER privilege:
set global sql_mode='IGNORE_SPACE';

select version(), AES_DECRYPT (AES_ENCRYPT('abc','y'),'y');
+----------------+------------------------------------------+
| version()      | AES_DECRYPT (AES_ENCRYPT('abc','y'),'y') |
+----------------+------------------------------------------+
| 5.0.17-max-log | abc                                      |
+----------------+------------------------------------------+

Personally, I'd remove the whitespace, so your code works in all environments.

Upvotes: 2

juergen d
juergen d

Reputation: 204746

Use LEAST()

SELECT `DATE`, 
       `PRICE1`, 
       `PRICE2`, 
        least(`PRICE1`, `PRICE2`) AS MINIMUM_OF_P1P2
FROM `TABLE`

Upvotes: 1

Related Questions