return the value with the highest ID, of a predetermined set of IDs in mysql

Question

I am attempting to pull the value associated with the highest detailID of a set of detailIDs that are predefined for each patientID.

here is an example of the table (also available as a fiddle):

CREATE TABLE `patient_details` (
    patientId INT UNSIGNED NOT NULL,
    detailId INT UNSIGNED NOT NULL,
    `value` VARCHAR(256),
    PRIMARY KEY (patientId, detailId),
    UNIQUE INDEX details_of_patient (patientId, detailId)
);

INSERT INTO patient_details (patientId, detailId, `value`)
  VALUES
(123, 75, '01'),
(123, 98, '02'),
(123, 151, '03 hit'),
(123, 251, '04'),

(321, 65, '05'),
(321, 75, '04'),
(321, 98, '03'),
(321, 151, '02 hit'),
(321, 180, '01'),

(123456, 75, '01'),
(123456, 89, '12/01/2022'),
(123456, 151, '03 hit'),
(123456, 215, '5681'),

(678910, 75, '01'),
(678910, 151, '03'),
(678910, 203, '12/01/2022 hit'),
(678910, 215, '56813')
;

What I need to do is pull the value of the highest detailID of 75, 151, 203.

I have tried using if function, to test the detailID but get syntax errors.

logically I am thinking I ought to be able to do something like this nested IF

select 
patientId,
table.value

if(detailid=203,set @largest_detailID=203,if(detailid=151,set @largest_detailID=151,if(detailid=75,set @largest_detailID=75,)))

from table

where detailID=@largest_detailID

What I would expect as a result

patientID	value
123	03 hit
321	02 hit
123456	03 hit
678910	12/01/2022 hit

While there are a number of questions and answers on this site addressing getting non-aggregate columns from rows corresponding to a maximum or minimum value (such as "Retrieving the last record in each group", "MySQL Left Join + Min"), they don't select from a limited list of values, nor is it trivial to adapt the answers to add this restriction.

Answer 1

Instead of trying to mess with all the IF's and @ variables, why not use a descending order of the detailID to help instead, and then add a LIMIT 1 to get only the highest reference that exists based on the 3 detailID numbers from your criteria:

Try with:

SELECT patientId, value
  FROM patientInfo
 WHERE detailID IN (75, 151, 203)
 ORDER BY detailID DESC
 LIMIT 1;

... and since a detailID of 203 and 89 does not exist in the query results will get the expected entry:

Example dbfiddle.