Can I use Max and Count Together?

[andrewteg]

Always finding answers here so finally posting a question!
I have a MySQL query and I am trying to get a max count to help replicate an Access Pivot table.
Query:

SELECT DISTINCT oTech, Count(sysID) as totRows
FROM view_schedule where SchedDate > '2007-08-01'
GROUP BY oTech, SchedDate
ORDER BY oTech ASC, totRows DESC;

Returns a table like this:
oTech.................totRows
4..............................7
4..............................3
4..............................1
6..............................2
6..............................1
7..............................1
9..............................2

Problem:
I only need the top row for each different oTech. I can skip other rows in my PHP code but was curious if there was a way to just get the maximum count for each oTech...

[pbmods]

Heya, Andrew. Welcome to TSDN!

Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

Try adding a LIMIT clause:

SELECT
    DISTINCT
        `oTech`,
        COUNT(`sysID`)
            AS `totRows`
    FROM
        `view_schedule`
    WHERE
        `SchedDate` > '2007-08-01'
    GROUP BY
        `oTech`,
        `SchedDate`
    ORDER BY
        `oTech` ASC,
        `totRows` DESC
    LIMIT 1;
 

[andrewteg]

Thanks, I appreciate the tip and help. However, I want the first data row each time the oTech is different so I want this data so LIMIT 1 does not work. I do know how to go through it in PHP to skip data I don't need but was looking for a way to do it in MySQL.

Data I Want
oTech.................totRows
4..............................7
6..............................2
7..............................1
9..............................2

[pbmods]

Heya, Andrew.

Oh I see.

Maybe this will work:

SELECT
    DISTINCT
        `oTech`,
        MAX(COUNT(`sysID`)) -- <--
            AS `totRows`
    FROM
        `view_schedule`
    WHERE
        `SchedDate` > '2007-08-01'
    GROUP BY
        `oTech`,
        `SchedDate`
    ORDER BY
        `oTech` ASC,
        `totRows` DESC
 

Otherwise, I'm thinking that you'd need to use a subquery to do this.

Maybe something like:

SELECT
        `oTech`,
        MAX(`totRows`)
            AS `totRows`
    FROM
    (
        SELECT
            DISTINCT
                `oTech`,
                COUNT(`sysID`)
                    AS `totRows`
            FROM
                `view_schedule`
            WHERE
                `SchedDate` > '2007-08-01'
            GROUP BY
                `oTech`,
                `SchedDate`
            ORDER BY
                `oTech` ASC,
                `totRows` DESC
    )
    GROUP BY
        `oTech`
 

EDIT: Is the DISTINCT qualifier necessary here? Not sure.

[andrewteg]

Thanks, I added "AS VS" alias after the sub-query end parenthesis and before the main query group by and that works great. The DISTINCT was not needed and actually got in the way of other data oddly enough so I removed it.

Thanks for your help!
Andrew

[pbmods]

Heya, Andrew.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)