Returning an extra column using SELECT (max) T-SQL

Question

First post, please be beautiful. [SQL Server 2014]

I am currently running selection against a table that looks like below

select ExerciseCultures.Name, max (convert (decimal (10,2), cast(weight as float))) as [Max Weight (KG)] from workoutsessions join ExerciseCultures on workoutsessions.ExerciseID = ExerciseCultures.ExerciseID group by ExerciseCultures.Name 

Which returns 31 rows, one for each exercise identifier, showing the maximum "weight" value for each of them.

I need an extra column that also shows a date for each of these rows. The date column is the column of the workoutsessions table next to each row.

I tried to add this date column using below:

 select ExerciseCultures.Name, max (convert (decimal (10,2), cast(weight as float))) as [Max Weight (KG)], workoutsessions.date from workoutsessions join ExerciseCultures on workoutsessions.ExerciseID = ExerciseCultures.ExerciseID group by ExerciseCultures.Name, workoutsessions.date 

But this returns 286 rows - all rows in the parent table. I need the original query results only with their corresponding date from the workoutsessions table.

Any thoughts?

Many thanks

Answer 1

 ;WITH CTE AS ( SELECT e.Name, CONVERT(DECIMAL(10,2),CAST(weight AS FLOAT))) [Max Weight (KG)], w.[date], RN = ROW_NUMBER() OVER( PARTITION BY e.name ORDER BY CONVERT(DECIMAL(10,2),CAST(weight AS FLOAT))) DESC) FROM workoutsessions w INNER JOIN ExerciseCultures e ON w.ExerciseID = e.ExerciseID ) SELECT name, [Max Weight (KG)], [date] FROM CTE WHERE RN = 1 

Answer 2

 SELECT A.NAME, A.[max weight (kg)], B.date FROM (SELECT exercisecultures.NAME, Max (CONVERT (DECIMAL (10, 2), Cast(weight AS FLOAT))) AS [Max Weight (KG)] FROM workoutsessions JOIN exercisecultures ON workoutsessions.exerciseid = exercisecultures.exerciseid GROUP BY exercisecultures.NAME) A JOIN workoutsessions B ON a.NAME = b.NAME AND CONVERT (DECIMAL (10, 2), Cast(B.weight AS FLOAT)) = A.[max weight (kg)] 

Answer 3

Using CROSS APPLY

 SELECT ExerciseCultures.Name, ws.[weight], ws.[date] FROM ExerciseCultures CROSS APPLY (SELECT TOP 1 [weight], [date] FROM workoutsessions WHERE ExerciseCultures.ExerciseID = ExerciseID ORDER BY [weight] desc) ws