Grouping Query using Max function

[cantona]

I have a basic query which lists the courses that each team member is currently attending. There is more than one row per person as many are attending more than one course. What I would really like to do is display each person once, and only display the course details for the course that takes up the most hours. For Example

PersonID, CrsCode, Hours
123 ABC1 26
123 DEF1 33

I would want to display this as;

123 DEF 33

Ive tried grouping on personID and using the Max of Hours, but this wont then display the Crs Code.

I know this is probably something simple! But its been a really long day!! Any suggestions??

 

[HarleyQuinn]

Get the max in a subquery and join it back into the main query, something like:

SELECT T2.PersonID, T2.CrsCode, T2.Hours
FROM Table2 T2,
(SELECT Table2.PersonID, max(Table2.Hours) as MostHours
FROM Table2
group by Table2.PersonID) MaxHours
Where T2.PersonID = MaxHours.PersonId
and T2.Hours = MaxHours.MostHours

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.