TOP on INNER JOIN-CodePudding

I have a table FISH which has a JOIN on HEALTHCHECK.

FISH

ID | NAME 
----------------
1 | JAMES
2 | JOHN
3 | ERIC

HEALTHECK

ID | DATE | FISH_ID
-------------------
1 | 2022-03-01 | 1
2 | 2023-01-01 | 1
3 | 2021-01-03 | 2
4 | 2023-04-07 | 3

I want to select a fish and return only the most recent HEALTHCHECK.

I have tried emulating this answer with:

SELECT fish.name, fish.id
FROM FISH
JOIN HEALTH_CHECK
ON HEALTH_CHECK.FISH_ID =
   (
   SELECT TOP 1  HEALTH_CHECK.CATCH_DATE
   FROM HEALTH_CHECK
   WHERE FISH_ID = HEALTH_CHECK.FISH_ID
       );

But the syntax of that is not correct, and I only want to return a specific fish (fish.id=x).

How would I return a fish with selected fields from the newest Health Check ?

The database is H2.

CodePudding user response：

I would use a pure join version here:

SELECT f.NAME, f.ID
FROM FISH f
INNER JOIN HEALTH_CHECK hc1 ON hc1.FISH_ID = f.ID
INNER JOIN
(
    SELECT FISH_ID, MAX(DATE) AS MAX_DATE
    FROM HEALTH_CHECK
    WHERE FISH_ID = <some ID>
    GROUP BY FISH_ID
) hc2
    ON hc2.FISH_ID = hc1.FISH_ID AND
       hc2.MAX_DATE = hc1.DATE
WHERE
    f.ID = <some ID>

A better approach might be to use ROW_NUMBER(), but only fairly recent versions of H2 support that.