Query Statement: Using DISTINCT but also using MAX condition

Question

I have a table in my db which is minimally represented below. Note that there are entries under PRV that are duplicates.

PRV	MV	AU
G-111	500	LR
G-112	400	LC
G-112	401	LA
G-200	650	LI
G-115	350	LR
G-115	0	LC
G-115	0	LA
G-116	0	LR

I used the following query statement to get a count of the number of distinct PRV grouped according to AU.

SELECT
    COUNT(*),
    AU
FROM
    (
        SELECT DISTINCT
            PRV,
            AU
        FROM
            tablename
        WHERE
            MV > O
    ) AS K
GROUP BY
    AU

RESULT:

COUNT(*)	AU
2	LR
1	LC
0	LA
1	LI

What I can't figure out is how to make sure that the grouping is not just based on MV>0 but also on MV being the max among duplicate PRVs.

For the last 3 rows (where PRV = 'G-115'), my query statement works alright because the row with AU = LR has MV=350 while the others have zero. Thus, G-115 is counted as just one unit and grouped under LR.

But, in the case of the 2nd and 3rd row (where PRV = 'G-112'), it is indeed counted as just 1 (because of the DISTINCT keyword), but I want it to be grouped as LA (not LC) because LA's MV = 401 (which is greater than MV=400 in the LC row).

So, the desired result would be:

COUNT(*)	AU
2	LR
0	LC
1	LA
1	LI

I read about MAX keyword but I can't figure out how to use it in my case.

In my actual table, the distinct keyword doesn't seem to work for rows 2 and 3. They are actually counted twice because MV > 0 for both entries.

Answer 1

SELECT
  m.AU,
  COUNT(x.MV) 
FROM mytable m
LEFT JOIN (
  SELECT
    PRV,
    MAX(MV) as MV
  FROM mytable
  GROUP BY PRV) x ON x.PRV = m.PRV and x.MV = m.MV
WHERE m.MV > 0
GROUP BY m.AU;

see: DBFIDDLE

The left join, to the subquery which calculates the MAX value for MV does make sure you do not count the LC value, because it does not have the MAX value.

output:

AU	COUNT(x.MV)
LR	2
LC	0
LA	1
LI	1