合并选择前 1 条 SQL 语句
我有 4 个选择语句:
I have 4 select statements:
SELECT top 1 software, auditDate, versionNumber FROM table1 where software = 'software1' order by auditDate desc
SELECT top 1 software, auditDate, versionNumber FROM table1 where software = 'software2' order by auditDate desc
SELECT top 1 software, auditDate, versionNumber FROM table1 where software = 'software3' order by auditDate desc
SELECT top 1 software, auditDate, versionNumber FROM table1 where software = 'software4' order by auditDate desc
目前，每个 select 语句都返回一个包含一行的表，如下所示:
Currently, each select statement returns a table with one row like this:
software | auditDate | versionNumber
1| software1 | 8/22/2017 | 5.0
如果 top 1 条件不存在，那么结果表将有更多行具有不同 auditDate 和 versionNumber，但我只需要每个软件的最新记录 (auditDate).
If the top 1 condition wasn't there, then the result table would have more rows of the same software with different auditDate and versionNumber, but I only needed the most recent record (auditDate) for each software.
我想将所有这些选择语句合并到一个查询中，该查询返回一个类似于以下的表:
I'd like to roll all these select statements into one query that returns a table similar to this:
software | auditDate | versionNumber
1| software1 | 8/22/2017 | 5.0
2| software2 | 8/20/2017 | 5.3
3| software3 | 8/21/2017 | 4.9
4| software4 | 8/16/2017 | 5.6
其中每一行都与上面的单个 select top 1 语句相同.
where each row is the same as the individual select top 1 statement above.
由于 order by 子句，UNION 似乎不起作用，我对其他解决方案感到困惑.
A UNION doesn't seem to work because of the order by clause and I'm stumped for other solutions.
推荐答案
另一个选项是使用 WITH TIES 子句
Another Option is using the WITH TIES clause
Select top 1 with ties
software, auditDate, versionNumber
From table1
Where software IN ('software1','software2','software3','software4')
Order By Row_Number() over (Partition By software Order By auditDate Desc)
相关文章