[RESOLVED] Fetch most recent record

[kebo]

Hey all,

I need a little help with a query on a MySQL server

I have 5 tables that I am trying to join; line, tank, chemicals, chemistries and result.

The line table has a one-many relation with the tank table (one line has many tanks)
The tank table has a one-many relation with the chemistries table (one tank has many chemistries)
The chemical table has a one-one relation with the chemistries table. (a chemistry references a chemical)
The result table holds daily measurement results for each chemistry.

I can use the following SQL to pull the lines, tanks and chemicals and chemistries together,

	SELECT L.Name AS Line,
			 T.Name AS Tank, 
			 CHEM.Name AS ChemicalName
	
	FROM line_table L
	
	JOIN tank_table T
		ON T.LineID = L.id
	
	JOIN chemistries_table CMST
		ON T.id = CMST.TankId
	
	JOIN chemicals_table CHEM
		ON CMST.ChemicalId = CHEM.id

What I am having trouble with is pulling the most recent result for each chemistry that is returned in the above SQL. I have tried adding the following, but simply adds information for the chemistry that is in the last record of the result table,

	LEFT JOIN 
		(
			SELECT *
			FROM titration_results_table
			WHERE id = (SELECT MAX(id) FROM titration_results_table)
		) R
		ON CMST.id=R.ChemistryID

What I need is to get the last record for each chemistry added to the result table.

Does anyone know how to do this?

Thanks
Kevin

[digitalShaman]

try this:

	SELECT L.Name AS Line,
			 T.Name AS Tank, 
			 CHEM.Name AS ChemicalName
	
	FROM line_table L
	
	JOIN tank_table T
		ON T.LineID = L.id
	
	JOIN chemistries_table CMST
		ON T.id = CMST.TankId
	
	JOIN chemicals_table CHEM
		ON CMST.ChemicalId = CHEM.id
	
	WHERE NOT EXISTS (SELECT id FROM chemistries_table WHERE id > CMST.id AND chemicalName=CMST.chemicalName)

assuming that your initial query returns all the rows that you want but more than that in the sense that you get multiple chemicals with the same chemicalName and you are only interesed in the ones with the highest id.
This is how i understood your question.

[kebo]

Thank you for the reply dS, but I don't think I was clear in the post. This is the complete SQL that I have with the result below,

	SELECT L.Name AS Line,
			 T.Name AS Tank, 
			 CHEM.Name AS ChemicalName,
			 CMST.id AS ChemistryId,
			 R.TitrationDate AS TitrationDate,
			 R.Value AS Result
	
	FROM line_table L
	
	JOIN tank_table T
		ON T.LineID = L.id
	
	JOIN chemistries_table CMST
		ON T.id = CMST.TankId
	
	JOIN chemicals_table CHEM
		ON CMST.ChemicalId = CHEM.id
		
	LEFT JOIN 
	(		
		SELECT *
		FROM titration_results_table
		WHERE id = (SELECT MAX(id) FROM titration_results_table)
	) R
	ON CMST.id=R.ChemistryID

As you can see, I am only returning values in the last 2 columns for a single record which make sense because the last join in the SQL is looking for the MAX id in the result table. What I need to do is to find the record from the result table with the max id for each ChemistryId returned in the table above.

Kevin

[digitalShaman]

OK, so a plain

	LEFT JOIN titration_results_table R
		ON CMST.id=R.ChemistryID

Would Join you all the result for each ChemistryID, so too many rows.
Now, if you add a

	WHERE NOT EXISTS (SELECT id FROM titration_results_table WHERE id > R.id AND ChemistryID = R.ChemistryID)

that should remove the rows that are not the highest id per ChemistryID

So that should be what you are looking for:

SELECT L.Name AS Line,
			 T.Name AS Tank, 
			 CHEM.Name AS ChemicalName,
			 CMST.id AS ChemistryId,
			 R.TitrationDate AS TitrationDate,
			 R.Value AS Result
	
	FROM line_table L
	
	JOIN tank_table T
		ON T.LineID = L.id
	
	JOIN chemistries_table CMST
		ON T.id = CMST.TankId
	
	JOIN chemicals_table CHEM
		ON CMST.ChemicalId = CHEM.id
		
	LEFT JOIN titration_results_table R
		ON CMST.id=R.ChemistryID

	WHERE NOT EXISTS (SELECT id FROM titration_results_table WHERE id > R.id AND ChemistryID = R.ChemistryID)

[digitalShaman]

Staying more with your approach, this should also work:

	LEFT JOIN 
	(		
		SELECT *
		FROM titration_results_table T1
		WHERE NOT EXISTS (SELECT id FROM titration_results_table WHERE id > T1.id AND ChemistryID = T1.ChemistryID)
	) R
	ON CMST.id=R.ChemistryID

Which would not just give you the one row with the highest id but all the rows with the highest id per ChemistryID.

[kebo]

Ty dS. That took care of the issue.

[Zvoni]

*sigh*
And if it's MySQL8 or higher, it's pretty simple with a ROW_NUMBER (instead of jumping through those burning hoops)
But for that i'd need your table structures