在SQL Server的列中查找最高值

关联子查询通常具有良好的性能:

select t.*
from t
where t.version = (select MAX(t2.version) from t t2 where t2.projectname = t.projectname);

尤其是,您希望 (projectname, version) 以获得最佳性能。

我会用 subquery :

select t.*
from table t
where version# = (select max(t1.version#) 
                  from table t1 
                  where t1.projectname = t.projectname
                  );

row_number() 功能:

select top (1) with ties *
from table t
order by row_number() over (partition by projectname order by version# desc);

SELECT p.projectname, p.versionno,p.startdata,p.enddate 
FROM project p 
INNER JOIN 
( SELECT projectname, MAX(startdate) MaxDate 
FROM project GROUP BY projectname ) 
Dates ON p.projectname = Dates.projectname
 AND p.startdate = Dates.MaxDate