I need help with filtering result rows returned by a query. I have a Illnesses table with columns percent, illness, date.
SELECT I.percent, I.illness, I.date
FROM Illnesses I
INNER JOIN Person P on I.person_id = P.id // an inner join I use in where clause
WHERE P.id = someId // 13 let's say
So in my case for Person with id = 13 query'll return
51, someIllness, 2015-12-07
43,someOtherIllness, 2015-12-08
null, anotherIllness, 2015-12-08
I need to select the row where date is most recent, and highest percent if there are multiple rows with that date. I added the following code in where clause
AND I.date = (SELECT max(I2.date)
FROM Illnesess I2
INNER JOIN Person P2 on I2.person_id = P2.id
WHERE P2.id = P.id)
AND I.percent = (SELECT max(I2.percent)
FROM Illnesess I2
INNER JOIN Person P2 on I2.person_id = P2.id
WHERE P2.id = P.id AND I2.date = I.date)
This will return 43,someOtherIllness, 2015-12-08 and will work fine at least I think so. There is an Exception I need to cover, if for the most recent date percent is null I need to take the row with previous date that have percent.
51, someIllness, 2015-12-07
null,someOtherIllness, 2015-12-08
null, anotherIllness, 2015-12-08
51, someIllness, 2015-12-07 will be the result I need.
If anyone help I'll be grateful. Thanks.


Check Solution