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:

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

CodePudding user response：

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.

CodePudding user response：

Assuming you want to apply this logic across multiple patients, try using ROW_NUMBER() to partition the data by patientId then sort based on the highest detailId:

WITH cte AS (
    SELECT * 
           , ROW_NUMBER() OVER(
                 PARTITION BY PatientId 
                 ORDER BY DetailID DESC
            ) AS RowNum
    FROM   YourTable
    WHERE  DetailId IN (75, 151, 203)
)
SELECT * 
FROM   cte 
WHERE  RowNum = 1

Sample Data:

Results:

db<>fiddle here