0

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.

FerozKhan
  • 11
  • 2
  • I mean to say retrieve maximum of testno under the same patientID in the table. What will be the SQL statement to achive this, – FerozKhan Oct 13 '10 at 08:38

2 Answers2

0

Use this SQL:

SELECT MAX(TestNo), PatientID
FROM dbo.YourTable
GROUP BY PatientID
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

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
  • 15,267
  • 10
  • 70
  • 113
  • If I want to pass a specific patientID,say BPL000019 what changes should I make – FerozKhan Oct 13 '10 at 08:44
  • 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 – FerozKhan Oct 13 '10 at 09:11
  • 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 Oct 13 '10 at 13:54
  • I am awaiting your reply for my question. – Kangkan Oct 14 '10 at 05:58
  • 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 Oct 14 '10 at 07:17