Returning extra column with SELECT (max)T-SQL

Question

First post so please be nice. [SQL Server 2014]

I'm currently running a select against a table which looks like the 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 exerciseID showing the highest 'weight' value for each one.

What I need is an additional column which also shows the date for each of these rows. The date column is already a column in the workoutsessions table alongside each row.

I've tried adding this date column, using the 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 then returns 286 rows -- all of the rows in the parent table. What I need is the original query results with just their respective 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