How to echo column name when max — Find highest value

[xfire123]

Hello!!! Any idea how to accomplish this:

I want to echo the column name and the value of

max($obj->CAVCH1,$obj->CAVCH2,$obj->CAVCH3)

Here is the other part of the code

$serverName = "IP"; //serverName\instanceName
$connectionInfo = array( "Database"=>"DBname", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

$result = sqlsrv_query($conn,"SELECT TOP (1) Events.EventDate, AVDCAV.CAVCH1, AVDCAV.CAVCH2, AVDCAV.CAVCH3 FROM AVDCAV INNER JOIN Events ON AVDCAV.IDEvent=Events.EventID WHERE CAVCH1 > 0 or CAVCH2 > 0 or CAVCH3 > 0 ORDER BY AVDCAV.IDEvent DESC");
$obj = sqlsrv_fetch_object( $result );
$maxObj = max($obj->CAVCH1,$obj->CAVCH2,$obj->CAVCH3);

Now i can only to echo the biggest value from the this 3 columns. The numbers are like 0.123 etc.

[Barand]

Your primary problem is your database design. When you have columns with names like xxx1, xxx2, ... , xxxN then that is sure sign of bad design and that the data needs to be normalized.

 

You should have something like

events
+---------+-------------+
| eventID | eventDate   |
+---------+-------------+
|    1    | 2017-11-29  |
|    2    | 2017-12-24  |
+---------+-------------+ 
     |
     +--------------+
                    |
            avdcav  | 
            +---------+----------+------------+
            | IDevent |  cavch   |   amount   |
            +---------+----------+------------+ 
            |    1    |     1    |   0.102    |
            |    1    |     2    |   0.116    |
            |    1    |     3    |   0,109    |
            |    2    |     1    |   0.128    |
            |    2    |     2    |   0.163    |
            |    2    |     3    |   0.201    |
            +---------+----------+------------+

This now makes it simple to query for the MAX() value for each event.

[xfire123]

Nice example thanks. I deal with similar DB structure in another topic in the forum. But in this two cases i'm not the designer of this DBs. But i got the idea.

Someone told me to create a view that returns a normalized result. At this time i don't have any experience for that.

In my example i get the last records of all columns for the 'max'. I don't see what query will do that for this example. And how to display the name(number) of column fom the max cavch in your example?

Edited November 17, 2017 by xfire123

[Barand]

Using my example, the query would be

            
SELECT a.IDevent
     , a.cavch
     , mx.amount
FROM avdcav as a
     INNER JOIN                             -- join to find the record for each event matching max value
     (
     SELECT IDevent                         -- subquery to find max value for each event
          , MAX(amount) as amount
     FROM avdcav
     GROUP BY IDevent
     } as mx USING (IDevent,amount)

[Barand]

If yu have to create a view then this query can be used to replicate the table in my example

SELECT IDevent
     , 1 as cavch
     , cavch1 as amount
FROM avdcav
UNION
SELECT IDevent
     , 2 as cavch
     , cavch2 as amount
FROM avdcav
UNION
SELECT IDevent
     , 3 as cavch
     , cavch3 as amount
FROM avdcav
ORDER BY IDevent, cavch

[xfire123]

Great, thank you. And still can i echo cavch number from corresponding to the MAX amount in your example?

[Barand]

Yes, it will be the numeric value (1,2 or 3) though instead of the corresponding column name (eg CAVCH1)