Jump to content

How to echo column name when max — Find highest value


xfire123

Recommended Posts

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.