1mocpws / Nested Query

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE Admissions (
  admission_id INT AUTO_INCREMENT KEY,
  patient_id INT,
  name VARCHAR(255),
  species VARCHAR(255),
  sex VARCHAR(255),
  presenting VARCHAR(255)
);

INSERT INTO Admissions(patient_id, name, species, sex, presenting)
	VALUES (1, 'dave', 'walrus', 'female', 'captured');
INSERT INTO Admissions(patient_id, name, species, sex, presenting)
	VALUES (2, 'steve', 'guinea pig', 'male', 'injured');
    
    
    
CREATE TABLE Observations (
  obs_id INT AUTO_INCREMENT KEY,
  patient_id INT,
  obs_date DATE,
  obs_severity_score INT,
  obs_bcs_score INT,
  obs_age_score INT
);

INSERT INTO Observations(patient_id, obs_date, obs_severity_score, obs_bcs_score, obs_age_score)
	VALUES (1, '2025-01-01', 1, 2, 1);
INSERT INTO Observations(patient_id, obs_date, obs_severity_score, obs_bcs_score, obs_age_score)
	VALUES (1, '2025-01-02', 1, 2, 1);
INSERT INTO Observations(patient_id, obs_date, obs_severity_score, obs_bcs_score, obs_age_score)
	VALUES (2, '2025-01-03', 1, 1, 1);    
INSERT INTO Observations(patient_id, obs_date, obs_severity_score, obs_bcs_score, obs_age_score)
	VALUES (1, '2025-01-04', 1, 1, 1);

-- QUERY database

-- # Admissions
-- SELECT *
-- FROM Admissions;

-- # Observerations
-- SELECT *
-- FROM Observations;

-- # Observations with ROW_NUMBERALTER
-- SELECT ROW_NUMBER() OVER(PARTITION BY O.patient_id ORDER BY O.obs_date DESC) RowNumber
--      , O.*
-- FROM Observations O;

-- # Combined
SELECT Admissions.admission_id
     , Admissions.name
     , Admissions.species
     , Admissions.sex
     , Admissions.presenting
     , Observations.obs_date
     , Observations.obs_severity_score
     , Observations.obs_bcs_score
     , Observations.obs_age_score
FROM Admissions
INNER JOIN -- or: LEFT JOIN
(
    SELECT ROW_NUMBER() OVER(PARTITION BY O.patient_id ORDER BY O.obs_date DESC) RowNumber
         , O.*
    FROM Observations O
) Observations
	ON Admissions.patient_id = Observations.patient_id
    AND Observations.RowNumber = 1

admission_id	name	species	sex	presenting	obs_date	obs_severity_score	obs_bcs_score	obs_age_score
1	dave	walrus	female	captured	2025-01-04	1	1	1
2	steve	guinea pig	male	injured	2025-01-03	1	1	1