How to get the first occurrence of a record from a table?

i also have a query like this !

select DISTINCT(HospitalName),AvgTotalPatients,TotalPatient from TotalPatients ORDER BY HospitalName,AvgTotalPatients,TotalPatient;

Hospitalnames are unique and AvgTotalPatients is also unique for every hospital now what i get is all records of all hopsitals reason is because in column TotalPatient there are entered many records for every hoapital so it takes all of them, what i want that it should select distinct hospitals with their AVgTotalPatients and only first occurence of TotalPatient.

what should i do? please help urgent

It sounds like you are aggregating data in some fashion.

It might be easier to help you find a good solution if you provided the code that creates [TotalPatients], or at least a better understanding of what/how [AvgTotalPatients] and [TotalPatient] really means.

However, your thinking 'should' be somewhat like this:

Code Snippet

SELECT
HospitalName,
AvgTotalPatients = avg( TotalPatients ),
TotalPatient = min( TotalPatients )
FROM TotalPatients
GROUP BY HospitalName
ORDER BY
HospitalName

|||

Thnx Arnie. I found solution to it 2 days back and it was something like this !

select DISTINCT(TP.HospitalName),TP.AvgTotalPatients,TP.TotalPatient from TotalPatients TP where TotalPatient = ( Select TOP 1(TotalPatient ) from TotalPatients where HospitalName = TP.HospitalName) ORDER BY HospitalName,AvgTotalPatients,TotalPatient;

very complicated one but i got what i wanted

Regards