Query Challenge

[b4ever]

Hi all,

I have written a query wich wil get data out of difference tables using join's.

The results looks as follow:

ProjectName TaskDescription TaskOwner TaskId
--------------------------------------------------------------
Project-A Task for A User-A 45
Project-A Another task for A User-A 46
Project-B Task for B User-B 47
Project-B Task for B User-B 48
Project-B Task for B User-B 49
Project-B Another task for B User-B 50
Project-C Task for C User-C 51
Project-D Task for D User-D 52

Now I wan't mine results so, that from Project-B, every TaskDescription='Task for B',
only the one, with the highes TaskId will shown.

E.g.
ProjectName TaskDescription TaskOwner TaskId
--------------------------------------------------------------
Project-A Task for A User-A 45
Project-A Another task for A User-A 46
Project-B Task for B User-B 49
Project-B Another task for B User-B 50
Project-C Task for C User-C 51
Project-D Task for D User-D 52


I have tried it with MAX(TaskID) or something, but it won't work in my solution...

Can anybody help?

Best regards,
Leon

 

[PHV]

Something like this ?
SELECT A.ProjectName, A.TaskDescription, A.TaskOwner, A.TaskId
FROM yourTable A INNER JOIN (
SELECT ProjectName, TaskDescription, MAX(TaskId) highestTaskId FROM yourTable GROUP BY ProjectName, TaskDescription
) B ON A.ProjectName = B.ProjectName AND A.TaskDescription = B.TaskDescription AND A.TaskId = B.highestTaskId

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