How to select only the last date row of a joined Table?-CodePudding

I am joining Table B into A. Table A has the basic information I want to retrieve and also the unique ID.

Table B has multiple rows for each ID with another column with Dates. Now I only want to select the last Date of Table B and join in into A.

I found the MAX() function of SQL but it says the other fields are not in the GROUP BY clause or an aggregation function.

This is my (simplified) query:

SELECT
   MAX("B"."ENDDATE") AS FINALEND,
   "A."ID",
   "A"."COLOR",
   "A"."MAKE",
   "A"."WHEELS",
FROM "A"
JOIN "B" ON "A"."ID" = "B"."ID"

My expected result is for each ID a row with the basic information from Table A and the last Date from all matching rows from Table B. My result now is multiple rows for every row in B.

Do I need to add a GROUP BY for ever other column? Or what am I missing?

Thanks for any input :)

CodePudding user response：

On MySQL 8 , we can use ROW_NUMBER here:

WITH cte AS (
    SELECT a.*, b.ENDDATE,
           ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY b.ENDDATE DESC) rn
    FROM A a
    INNER JOIN B b ON b.ID = a.ID
)

SELECT ID, COLOR, MAKE, WHEELS, ENDDATE AS FINALEND
FROM cte
WHERE rn = 1;

On earlier versions of MySQL, we can join to a subquery which finds the latest record for each ID in the B table:

SELECT a.ID, a.COLOR, a.MAKE, a.WHEELS, b1.ENDDATE AS FINALEND
FROM A a
INNER JOIN B b1 ON b1.ID = a.ID
INNER JOIN
(
    SELECT ID, MAX(ENDDATE) AS MAXENDDATE
    FROM B
    GROUP BY ID
) b2
    ON b2.ID = b1.ID AND b2.MAXENDDATE = b1.ENDDATE;