Maximum value

[Johanna Gronlund]

Hello,

I have a table in the following format:
ID Diagnosis Fraction
1 X48 0.01
1 Y52 0.5
2 U78 0.85
etc

What I need is only those records that have the highest fraction for each ID.
ID Diagnosis Fraction
1 Y52 0.5
2 U78 0.85
etc

I have used the Max function in my query. However, that does not work
because the information is also grouped by diagnosis as well as ID and this
causes all of the lines to appear. Does anyone know how to get around this
problem? I can't just bring through Fraction and ID because there is no way
of linking the fraction to diagnosis afterwards. Many diagnosis have the same
fractions.

Many thanks for your help in advance.

Johanna

 

[Dale Fye]

How about something like:

SELECT ID, Diagnosis, Fraction
FROM yourTable T1
INNER JOIN (SELECT ID, MAX(Fraction) as MaxFraction
FROM yourTable
GROUP BY ID) as T2
ON T1.ID = T2.ID
AND T1.Fraction = T2.MaxFraction

This query uses a subquery to identify the ID and the maximum Fraction for
each ID. It then joins that subquery back to the original table to give you
just those records where the ID's match and the Fraction and MaxFraction are
equal.

Of course, this will return multiple records for a particular ID if there
are multiple diagnosis with the same fraction.

HTH
Dale

 

[Johanna Gronlund]

Thank you Dale, it was very helpful to have the explanation of the logic that
you applied as well as the SQL statement.

I now need to think about what to do with those admissions where the
fraction is equal but diagnosis different. Is there a way to select just one
diagnosis? It doesn't matter which one is selected. I just need to get rid of
the dublicates.

Johanna

 

[John Spencer]

You could try the following

SELECT ID, First(Diagnosis) as DX , Fraction
FROM yourTable T1
INNER JOIN (SELECT ID, MAX(Fraction) as MaxFraction
FROM yourTable
GROUP BY ID) as T2
ON T1.ID = T2.ID
AND T1.Fraction = T2.MaxFraction
GROUP BY ID, Fraction


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..