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

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

I have the following query:

SELECTNotifications.[TimeStamp])

FROMdbo.vwGrantsMaster LEFT OUTER JOIN

dbo.CoFundNotifications ON dbo.vwGrantsMaster.GrantFMBId = dbo.CoFundNotifications.GrantsFMBId

It is returning multiple records from Notifications table since it allows multiple entries under a single GrantFMBId. For example for a single GrantFMBId there can be multiple TimeStamp. When retrieved all are appearing even distinct key word is used.

What I am looking is that to get only the first occurrence of a record for a GrantFMBId from Notifications table.

Any help?

Thank you in advance.

Am I in the wrong place...?|||

If I understand you correctly, you wish the FIRST row entered for a particular GrantsFMBId, [TimeStamp] combination.

Perhaps something like this will produce your desired results:

Code Snippet

SELECT dt.TS
FROM (SELECT
GrantFMBId,
TS = min( [TimeStamp] )
FROM dbo.vwGrantsMaster
GROUP BY GrantFMBId
) dt
LEFT JOIN dbo.CoFundNotifications cf
ON dt.GrantFMBId = cf.GrantFMBId