I have a database (SQL server 2005 Express) table wth columns PatientID (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.
views:
35answers:
3
A:
Use this SQL:
SELECT MAX(TestNo), PatientID
FROM dbo.YourTable
GROUP BY PatientID
marc_s
2010-10-13 08:34:59
Thanks for your quick responce....I will try it and get back to you
Feroz Khan
2010-10-13 08:42:53
A:
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.
Kangkan
2010-10-13 08:35:35
If I want to pass a specific patientID,say BPL000019 what changes should I make
Feroz Khan
2010-10-13 08:44:34
I would like to retrieve only one record from the table which has the maximum testNo for a given PatientID . I am using ADO recordSet pointer to fetch the record so I need the correct SQL statement so that I can pass it to PrecordSet->Execute function. Kindly help
Feroz Khan
2010-10-13 09:11:26
What do you mean by maximum testNo? Is it maximum count of test [max(count(tesNo))] or the maximum of the value in testNo [max(testNo)]?
Kangkan
2010-10-13 13:54:07
A:
I am sorry for the delay Mr.KangKan....Among all the records which has same PatientID ,I would like to retrieve the maximum value entered in the TestNo column
FerozKhan
2010-10-14 07:17:10