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 🙂
On MySQL 8+, we can use
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;
Answered By – Tim Biegeleisen
Answer Checked By – Mary Flores (BugsFixing Volunteer)