SQL Server table to be retrieved using ADO in VC++

Question

I have a table with 2 columns, PatientID and TestNo. PatientID can be same for more than 1 records but the TestNo will be always distinct. I want to know the SQL statement which can fetch me the highest value in the TestNo field among all the records which has same PatientID basically max(TestNo) . I want to pass the specific PatientID in the SQL statement and the query must return the highest TestNo field value among the records which has the PatientID as passed in the SQL statement.

Answer 1

This will get you the max per Patient

SELECT  PatientID,
        MAX(TestNo)
FROM    YourTable
GROUP BY PatientID

This will get you the max number for pation 100

SELECT  MAX(TestNo)
FROM    YourTable
WHERE   PatientID = 100