How to pull data with latest date

[5lights]

Here's the scenerio:
Table has: TestID(unique), ClientID, TestType, TestDate, Score.

How do I query to get just the latest score for specific TestType?

My report already pulls other client data from other tables, but now I need to add latest test scores.

Please advise.
Thanks

 

[crystalreporting]

I don't believe this is something you can do with selection criteria as you will not know when the latest test date is. Add selection criteria for TestType, then sort all of the results ascending by date. Obviously, the last value in the list will be the latest test date - simply drag the fields out of the detail section, and into the report footer, then suppress the details section. The values that are shown in the report footer will be from the latest test date.

Peter Shirley

http://www.linkcorp.com

 

[5lights]

Here's what I've done so far:
SQL Server Ent Mgr: Created a new view with query:
SELECT CPatientID AS ID, TestType AS Type,
MAX(TestDate) AS [Date]
FROM dbo.CTests
WHERE (TestType = 'typename1') OR
(TestType = 'TestName2')
GROUP BY CPatientID, TestType
ORDER BY CPatientID

This gave me a table with only the latest testdates...
Now all I need to do is get the two different tests on a single line in the detail section....

One step closer to complete.
Thanks