selecting greatest value from multiple fields/rows

neljan

+----------+--------+--------+--------+
| recordID | value1 | value2 | value3 |
+----------+--------+--------+--------+
|     1    |   56   |   78   |    9   |
+----------+--------+--------+--------+
|     2    |   19   |   87   |   66   |
+----------+--------+--------+--------+

Would someone mind telling me the easiest way to select the greatest value from all rows (in this case 87) please?

Much abliged 🙂

bradgrafelman

Sounds like you simply want the MAX() of the MAX(); in other words, use a subquery to SELECT the MAX() values for each of the columns, and then, in the outer query, SELECT the MAX() of the values of that subquery.

EDIT: Woops, maybe it's not that easy...

EDIT2: I'll be humble enough to let my brief moment of ignorance remain visible, because it definitely wasn't that easy (it never is, is it?).

Instead, what I came up with was to UNION the individual max values from each column inside the inner query I talked about above so that you get a column vector of max values (which is what MAX() operates on), rather than a row vector.

In other words, something like this:

SELECT MAX(max_column) absolute_max
FROM
	(
	SELECT MAX(value1) max_column FROM my_table
	UNION
	SELECT MAX(value2) FROM my_table
	UNION
	SELECT MAX(value3) FROM my_table
	# etc. for all columns in the comparison
	) max_column_values
;

neljan

Damn I forgot something...

+----------+---------+---------+---------+---------+---------+---------+---------+---------+
| recordID | player1 | p1_scr1 | p1_scr2 | p1_scr3 | player2 | p2_scr1 | p2_scr2 | p2_scr3 |
+----------+---------+---------+---------+---------+---------+---------+---------+---------+
|    1     |  John   |   56    |   78    |    9    |  David  |   33    |   45    |   41    |
+----------+---------+---------+---------+---------+---------+---------+---------+---------+
|    2     |  Adam   |   19    |   87    |   66    |  John   |   16    |   20    |   29    |
+----------+---------+---------+---------+---------+---------+---------+---------+---------+
|    3     |  Peter  |   94    |   11    |   17    |  John   |   99    |   14    |   71    |
+----------+---------+---------+---------+---------+---------+---------+---------+---------+

I want to get John's highest score (99). John could be player1 - player7.

:queasy:

Btw thanks for your help mate 😉

bradgrafelman

First thing you should do is re-design the database (and do it properly this time 🙂). That table structure fails the first normal form (a few times over!). Might want to search for some decent articles on "database normalization" before you do, though.