How to select a specific row from a set of rows in SQL

Question

I need to select a specific row from a "set" of a admission data. It took me some time to try and understand the process and I'll try to explain what I need to achieve with an example.

So, below, I have a history of admission and readmissions to a hospital for a specific member. We can classify the below rows in the image as different admission sets.

Every new admission is given a CLM_NBR and if the member is re-admitted, for some reasons, as a follow-up to the previous admission, the READMIT_CLM_NBR of the initial admission/re-admission is equal to the CLM_NBR of the new, related re-admission. We call the very first admission of a set the original admission.

If an admission does not have a re-admission, the READMIT_CLM_NBR will be NULL.

The admission sets are as follows:

admission set 1 : Row number 4

admission set 2 : Row number 6, 7, 8

admission set 3 : Row number 5, 1, 3, 2

Now, I need to get the DATE_ADMITTED of the original admission in the last admission set (admission set 3). The highlighted row is the one that we need and a query should return 2016-05-18 as the value. I'm trying to figure out a way of partitioning or grouping by the dates but nothing seems to work.

Since we care only if there was a corresponding re-admission, all rows with NULL READMIT_CML_NBR can be discarded. The query should look like:

select READMIT_CLM_NBR, CLM_NBR, DATE_ADMITTED 
from P_Admission 
where READMIT_CLM_NBR in (select distinct CLM_NBR from P_Admission) and MBR_ID = '0610297305';

the query then returns the following result...

2

I need to get the date from the highlighted row. Any ideas on how this can be done?

Answer 1

You should be able to get the result you want by finding all CLM_NBR values that are not also a READMIT_CLM_NBR (so they are the original admission); these rows can then be ordered by DATE_ADMITTED and the row with the latest date selected:

WITH CTE AS (
    SELECT READMIT_CLM_NBR, CLM_NBR, DATE_ADMITTED,
           ROW_NUMBER() OVER (ORDER BY DATE_ADMITTED DESC) AS rn
    FROM P_Admission p1
    WHERE MBR_ID = '0610297305'
      AND NOT EXISTS (SELECT * FROM P_Admission p2 WHERE p2.READMIT_CLM_NBR = p1.CLM_NBR)
) 
SELECT READMIT_CLM_NBR, CLM_NBR, DATE_ADMITTED
FROM CTE
WHERE rn = 1

Demo (for MySQL 8 but generic SQL) on db-fiddle