CODEWITHSUNDEEP
mysqlsql
Mohit Singh
Mohit Singh

Reputation: 31

SQL - max of a column corresponding to two columns

A table contains four columns "server", "directory", "usage" and "datetime". All servers has got 10 dirs in common. I need to get the data for a server and it's any dir the usage for the latest datetime in a day. Say for example if there is a server A with directory B there will be Usage at multiple time for few days. I need the data to be reported by the query for all servers it's all corresponding directory's usage for the latest entry on each day.

Upvotes: 1

Views: 271

Answers (2)

Kamil
Kamil

Reputation: 13951

Im not sure that i understand correctly your question.

MySQL has IF() function, that returns one of statements according to condition in first parameter.

If you want to select data from column where number is bigger and there are only 2 columns - use IF statement like this:

SELECT 
if(columnA > columnB, columnA , columnB) as grtColumn,
if(columnA > columnB,'columnA is bigger', 'columnB is bigger') as whichColumnWasGrt

from yourtable;

Help: mysql reference - if() function (there are IF statement and IF() function - diffrent thigs!

Upvotes: 0

Daniel Geh
Daniel Geh

Reputation: 54

If I understood your question correctly, you want to see the last usage for every server and directory. Given a table named "usagestats" with the given columns that would be:

SELECT a.server, a.directory, a.`usage`, a.datetime
    FROM usagestats as a INNER JOIN (
        SELECT server, directory, max(datetime) datetime
            FROM usagestats
            GROUP BY server, directory
        ) AS b ON (
            a.server = b.server
            and a.directory = b.directory
            and a.datetime = b.datetime
        )
    ORDER BY a.server, a.directory, a.datetime

Upvotes: 1

Related Questions