Select Distinct troubles...

[jcandeli]

Thanks in advance for any help you can give me!

I have a query that I am trying to construct which seems simple in theory but my SQL skills are basic and I can't figure out how to get the results I am looking for.

Basically, I have a table which logs a person's exercises. It stores:
ExerciseName
Sets
Reps
Weight
ExerciseDate

For simplicty sake, let's say this is what is in my table:
Squats, 1, 12, 100, 2/17/06
Squats, 2, 12, 110, 2/19/06
Squats, 3, 10, 120, 2/20/06
Curls, 3, 10, 50, 2/17/06
Curls, 2, 15, 50, 2/20/06


I would like to display distinct exercises AND the LAST values entered for Sets, Reps, and Weight for each exercise.

in my example above it would be:
Squats, 3, 10, 120 (2/20/06)
Curls, 2, 15, 50 (2/20/06)


Sounds simple enough but I can't seem to construct a query that gives me the distinct exercise names and the last entered for each.

Any help is much appreciated.

Thank you!
JP

 

[PHV]

SELECT A.ExerciseName, A.Sets, A.Reps, A.Weight, A.ExerciseDate
FROM yourTable A INNER JOIN (
SELECT ExerciseName, MAX(ExerciseDate) LastDate FROM yourTable GROUP BY ExerciseName
) L ON A.ExerciseName = L.ExerciseName AND A.ExerciseDate = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jcandeli]

You rock!
Thanks for the quick response!
It seems to work fine.

JP

 

[jcandeli]

Hey, thanks for your help yesterday. I have one more question. If I wanted to add a "where" clause for another field called UserID how would I do that?

I tried adding "... where UserID = ..." but that doesn't work.

Thanks so much for your help again!
JP

 

[jcandeli]

Nevermind. I think I got it. Sorry!
JP