conditional-statementsmaxdistinct

# Query Statement: Using DISTINCT but also using MAX condition

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.

Solution

• ``````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