查询从每个组中获取前2

I have this sql code:

我有这个sql代码:

SELECT   NoteOID = HCN.ObjectID,
         PatientOID = HCN.PatientID,
         PatientVisitOID = 0,
         CollectedDT = HCN.CollectedDT
FROM     HClinicalNote HCN WITH(NOLOCK)
where    HCN.enddt is NULL
         and HCN.visitid in (select distinct visitOID from @tblCensus)
order by HCN.PatientID,HCN.CollectedDT desc

that give these results:

给这些结果:

 NoteOID    PatientOID     CollectedDT
 181382     890855         2011-09-14 21:31:00
 169115     890855         2011-09-12 18:38:00
 177466     890855         2011-09-09 19:49:00
 175150     890855         2011-09-07 19:34:00
 174057     890855         2011-09-06 19:25:00
 172429     890855         2011-09-04 09:00:00
 181387     13462666       2011-09-14 21:37:00
 182224     13462666       2011-09-14 13:24:00
 179269     13462666       2011-09-12 18:12:00

I would like to have the top 2 CollectedDT from each group of PatientOID.

我想从每组患者中选出前2名。

2 个解决方案

#1

If you're using at least SQL-Server 2005, you ca use a CTE with ROW_NUMBER function:

如果您正在使用至少SQL-Server 2005，您可以使用带有ROW_NUMBER函数的CTE:

WITH CTE AS(
   SELECT  NoteOID = HCN.ObjectID,
           PatientOID = HCN.PatientID,
           PatientVisitOID = 0,
           CollectedDT = HCN.CollectedDT,
           RN = ROW_NUMBER()OVER(PARTITION BY PatientOID ORDER BY CollectedDT ASC)
   FROM HClinicalNote HCN
   WHERE HCN.enddt is NULL
   AND HCN.visitid in (select distinct visitOID from @tblCensus)
)
SELECT * FROM CTE
WHERE RN <= 2

#2

select T2.* from (SELECT  NoteOID = HCN.ObjectID, 
       PatientOID = HCN.PatientID,   
       PatientVisitOID = 0,   
       CollectedDT = HCN.CollectedDT,  
       RN = ROW_NUMBER()OVER(PARTITION BY PatientOID ORDER BY CollectedDT ASC)  
FROM HClinicalNote HCN   
WHERE HCN.enddt is NULL  
AND HCN.visitid in (select distinct visitOID from @tblCensus) ) as T2   
where T2.RN >=2