Selecting column with highest value in SQL

Question

I have a table that is something like below

column1 | column2 | column3 | column4
   43        12        1        132

Obviously, the 'column4' has the highest value. So my goal is to retrieve this column.

Desire results:

   column4
     132

Aksed and answered previously: http://stackoverflow.com/questions/17845555/mysql-max-value-from-3-different-columns use GREATEST. fiddle example: http://sqlfiddle.com/#!2/4e850/1/0 — xQbert, Feb 17 '14 at 14:32
That's true, but how would I keep the name of the column? Your reference only tells you to get the value with the highest column. — user3135626, Feb 17 '14 at 14:36

score 6 · Answer 1 · answered Feb 17 '14 at 14:30

6

Try the function GREATEST():

SELECT GREATEST(column1, column2 colum3, column4)
FROM table

answered Feb 17 '14 at 14:30

Stephan B

3,671
20
33

This is good solution, but how would I keep the name of the column? – user3135626 Feb 17 '14 at 14:33
You can't using greatest and only have 1 column returned. A column couldn't have two names say greatest for row 1 is column 1 but column 4 for row 2. It can't be both, thus you must alias the column or get a system assigned name. – xQbert Feb 17 '14 at 14:35
Then why suggest the GREATEST() function? I clearly stated above, that I wanted to get the value and keep the name of the column. – user3135626 Feb 17 '14 at 14:38
One shouldn't "look the gift horse in the mouth", as it were. @user3135626 - Your comment sounds like you ordered Steak at a restaurant, and got Corned Beef instead. – Funk Forty Niner Feb 17 '14 at 14:41
Sorry, I was reading "want to retrieve this value" in your question. If you need the column name and value for the highest value for each row you should go with the answer of Gordon or parse the result in php. There is no need to be aggressive on SO just because your definition of "clearly stated" differs from mine. – Stephan B Feb 17 '14 at 14:47

score 5 · Accepted Answer · answered Feb 17 '14 at 14:38

5

If you want the column name:

select greatest(column1, column2, column3, column4) as biggestval,
       (case when column1 = greatest(column1, column2, column3, column4)  then 'column1'
             when column2 = greatest(column1, column2, column3, column4)  then 'column2'
             when column3 = greatest(column1, column2, column3, column4)  then 'column3'
             when column4 = greatest(column1, column2, column3, column4)  then 'column4'
        end) as biggestval_columnname

Note that this will not work if any of the columns have NULL values.

answered Feb 17 '14 at 14:38

Gordon Linoff

1,242,037
58
646
786

Cool! But how would I keep the name of the column? Such as column4: 132? – user3135626 Feb 17 '14 at 14:39
@user3135626 . . . This has two columns, one with the value and one with the column name. – Gordon Linoff Feb 17 '14 at 14:42

score 0 · Answer 3 · answered Feb 17 '14 at 14:29

0

Try this :

SELECT
   CASE
       WHEN column1 >= column2 AND column1 >= column3 THEN column1
       WHEN column2 >= column1 AND column2 >= column3 THEN column2
       WHEN column3 >= column1 AND Date3 >= column2 THEN column3
       ELSE  column1 
   END AS TopColumn

There isn't a SQL standard function that makes what you have requested

answered Feb 17 '14 at 14:29

Ganz

187
5

Is that from this? http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns – MackieeE Feb 17 '14 at 14:29
Yes! This is the logic solution from your post – Ganz Feb 17 '14 at 14:30

score 0 · Answer 4 · answered Feb 17 '14 at 14:36

0

Stock your value in a array and use function PHP MAX , Try this

$sql = 'SELECT column1, column2, column3, column4 FROM table';
$req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());
$t_number=array();
$i=0;
while($data = mysql_fetch_assoc($req)){
  $t_number[$i]=array(
        $data['column1'],
        $data['column2'],
        $data['column3'],
        $data['column4']
 );
   $maxValue=max($t_number[$i]);
   echo $maxValue;
   $i++;
}

answered Feb 17 '14 at 14:36

Guillaume Brion

51
6

1

This looks promising, however, in the future, please use `mysqli_*` instead of `mysql_*` based functions. Some may downvote this because of that, yet not me; it's not my style ;-) – Funk Forty Niner Feb 17 '14 at 15:31

Selecting column with highest value in SQL

4 Answers4