How do I retrieve data from SQL server table which has two columns say PatientID and TestNo, Differnt TestNo can have same patient ID

I have a database (SQL server 2005 Express) table wth columns Pati开发者_StackOverflowentID (more than 1 records can have same patient ID) and TestNo. I want to retrieve the maximum of testNo column among all the records with same PatientID.What should be the SQL statement to do so?I am using RecordSet pointer to access the records in a vc++ application.

Use this SQL:

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

The following query should do your work:

Select max(TestNo) as TestNo, PatientId from TableName group by PatientId

This will return you the max of the test no for each of the patient. You can add where condition if you need to take for a particular patient.