在SQL Server中的列中查找最高值

I have a SQL Server database that currently shows data in below format

我有一个SQL Server数据库,当前以下面的格式显示数据

As you can see each project has different version number stored in database. What I want to do is to have the project with the latest version number and its related dates displayed in the reports. Hence I used the Row_Number and Partition by but somehow I'm not getting the result especially when I am putting my CTE and Partition Logic in Stored Procedure.

如您所见,每个项目都有不同的版本号存储在数据库中。我想要做的是让项目在报告中显示最新版本号及其相关日期。因此我使用了Row_Number和Partition,但不知何故我没有得到结果,特别是当我将CTE和分区逻辑放入存储过程时。

The data that I want is in below format;

我想要的数据是以下格式;

3 个解决方案

#1

A correlated subquery usually has good performance:

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

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

In particular, you want an index on (projectname, version) for optimal performance.

特别是,您需要(projectname,version)上的索引以获得最佳性能。

#2

I would use subquery :

我会使用子查询:

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

However, you can also use row_number() function :

但是,您也可以使用row_number()函数:

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

#3

-2

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

#1