2

I'm using the mysql GREATEST() function to select the highest integer value from either column (apples or peaches).

+----+------------------+
| id | apples | peaches |
+----+------------------+
|  1 |    8   |    4    |
|  2 |    2   |    6    |
|  3 |    3   |    9    |
|  4 |    7   |    2    |
|  5 |    4   |    4    |
+----+------------------+

Using $result = "SELECT GREATEST(apples, peaches) FROM table"; and echo $result, I get: 

8
6
9
7

Next to each value, I want to echo the corresponding fruit name and a fruit icon. How can I achieve the image below via the MYSQL query?

alt text

Also, notice that equal values aren't displayed by the GREATEST() function. Is there a way to display the equal value as well?

Raidri
  • 17,258
  • 9
  • 62
  • 65
Noob
  • 541
  • 1
  • 8
  • 12

1 Answers1

4
SELECT 
IF(apples >= peaches, apples, peaches) AS quantity,
IF(apples >= peaches, 'apples', 'peaches') AS fruit
FROM ...

or, if you don't want apples to be the default on equal and want to know when both fruits are equally represented:

SELECT 
IF(apples >= peaches, apples, peaches) AS quantity,
CASE WHEN apples > peaches THEN 'apples' WHEN peaches > apples THEN 'apples' ELSE 'both' END AS fruit
FROM ...
Alin Purcaru
  • 43,655
  • 12
  • 77
  • 90
  • thanks your code works! I wanted to preserve the `greatest()` function so I implement your code into this working query `SELECT GREATEST(apples, peaches) as quantity, CASE WHEN apples > peaches THEN 'apples' WHEN peaches > apples THEN 'peaches' ELSE 'both' END AS fruit FROM ...` – Noob Dec 05 '10 at 01:08