First diagnostics, PostgreSQL

Language: Layout:

/*
I'm working with medical data and I want to find the primary diagnosis 
given to certain patient.

The diagnoses are all in one column as a comma separated string. 
For example "A10.3,B55.2, A10.1". 
Lets call this table patients and the column with the diagnoses diag_list. 
I want to create a new column that contains the first part from diag_list 
that has a match in different table that contains a list of diagnosis 
that could be considered "primary". 
Lets call this table ICD10 and the column with the list of primary 
diagnoses as primary_diag.
*/

DROP TABLE IF EXISTS patients ;
DROP TABLE IF EXISTS ICD10 ;

CREATE TABLE patients
(
    patient_id integer PRIMARY KEY,
    diag_list text NOT NULL
) ;

CREATE TABLE ICD10
(
    primary_diag text PRIMARY KEY,
    diagnose text
) ;

INSERT INTO patients 
VALUES
    (1, 'A10.3,B55.2, A10.1') ,
    (2, 'A10.3, A10.1, C20.2') ;

INSERT INTO ICD10
VALUES
    ('B55.2', 'Something Bad'),
    ('A10.1', 'Somehitng Worse');

/*
The reason I want just the first match is that diag_list is 
already according to the severity of the symptoms. 
So I'm trying to find the diagnosis with most severe symptoms 
that could also be considered a primary diagnosis.
*/

-- Step 1
SELECT
    patient_id, trim(diag) AS diag, nr
FROM
    patients
    JOIN LATERAL unnest(string_to_array(diag_list, ',')) 
         WITH ORDINALITY AS a(diag, nr) ON true ;

-- Step 2
WITH patients_and_diags AS
(
SELECT
    patient_id, trim(diag) AS diag, nr
FROM
    patients
    JOIN LATERAL unnest(string_to_array(diag_list, ',')) 
         WITH ORDINALITY AS a(diag, nr) ON true
)
SELECT
    patient_id, diag, nr, diagnose
FROM
    patients_and_diags
    JOIN ICD10 ON ICD10.primary_diag = patients_and_diags.diag ;

-- Final
WITH patients_and_diags AS
(
SELECT
    patient_id, trim(diag) AS diag, nr
FROM
    patients
    JOIN LATERAL unnest(string_to_array(diag_list, ',')) 
         WITH ORDINALITY AS a(diag, nr) ON true
)
, patients_and_ICD10 AS
(
SELECT
    patient_id, diag, nr, diagnose
FROM
    patients_and_diags
    JOIN ICD10 ON ICD10.primary_diag = patients_and_diags.diag
)
, first_ICD10 AS
(
SELECT
    patient_id, min(nr) AS nr
FROM
    patients_and_ICD10
GROUP BY
    patient_id
)
SELECT 
    * 
FROM 
    first_ICD10 
    JOIN patients_and_ICD10 USING(patient_id, nr)


absolute service time: 0,41 sec