英文:
SQL Select Rows with Highest Version
问题
我正在尝试查询以下表格：
| ID | ConsentTitle | ConsentIdentifier | Version | DisplayOrder |
|---|---|---|---|---|
| 1 | FooTitle | foo1 | 1 | 1 |
| 2 | FooTitle 2 | foo1 | 2 | 2 |
| 3 | Bar Title | bar1 | 1 | 3 |
| 4 | Bar Title 2 | bar1 | 2 | 4 |
我的表格中有具有唯一ConsentTemplateIdentifier的条目。我想只返回具有特定唯一标识符的最高版本号的行...
| ID | ConsentTitle | ConsentIdentifier | Version | DisplayOrder |
|---|---|---|---|---|
| 2 | FooTitle 2 | foo1 | 2 | 2 |
| 4 | Bar Title 2 | bar1 | 2 | 4 |
我的当前查询似乎不起作用。它告诉我：
在选择列表中，列'ConsentTemplates.ID'无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中
Select Distinct ID, ConsentTitle, DisplayOrder, ConsentTemplateIdentifier, MAX(Version) as Version
from FooDocs
group by ConsentTemplateIdentifier
如何选择具有其各自ConsentTemplateIdentifiers的最高Version号并按其DisplayOrder排序的行？我正在使用SQL Server。
英文:
I am trying to query the following table:
| ID | ConsentTitle | ConsentIdentifier | Version | DisplayOrder |
|---|---|---|---|---|
| 1 | FooTitle | foo1 | 1 | 1 |
| 2 | FooTitle 2 | foo1 | 2 | 2 |
| 3 | Bar Title | bar1 | 1 | 3 |
| 4 | Bar Title 2 | bar1 | 2 | 4 |
My table has entries with unique ConsentTemplateIdentifier. I want to bring back only the rows with the highest version number for that particular unique Identifier...
| ID | ConsentTitle | ConsentIdentifier | Version | DisplayOrder |
|---|---|---|---|---|
| 2 | FooTitle 2 | foo1 | 2 | 2 |
| 4 | Bar Title 2 | bar1 | 2 | 4 |
My current query doesn't seem to work. It is telling me:
> Column 'ConsentTemplates.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
Select Distinct ID,ConsentTitle, DisplayOrder, ConsentTemplateIdentifier, MAX(Version) as Version
from FooDocs
group by ConsentTemplateIdentifier
How do I select the rows distinctly which have the highest Version number for their respective ConsentTemplateIdentifiers ordered by their display order?
Any help would be really appreciated. I am using SQL Server.
答案1
得分: 2
你可以使用 CROSS APPLY 完成这个操作。
SELECT DISTINCT ca.*
FROM FooDocs fd
CROSS APPLY (SELECT TOP 1 *
FROM FooDocs
WHERE ConsentIdentifier = fd.ConsentIdentifier
ORDER BY Version DESC) ca
如果你的唯一标识有自己的表。
SELECT ca.*
FROM ConsentTable ct
CROSS APPLY (SELECT TOP 1 *
FROM FooDocs
WHERE ConsentIdentifier = ct.Identifier
ORDER BY Version DESC) ca
英文:
You can do this with CROSS APPLY.
SELECT DISTINCT ca.*
FROM FooDocs fd
CROSS APPLY (SELECT TOP 1 *
FROM FooDocs
WHERE ConsentIdentifier = fd.ConsentIdentifier
ORDER BY Version DESC) ca
If your unique identifier has it's own table.
SELECT ca.*
FROM ConsentTable ct
CROSS APPLY (SELECT TOP 1 *
FROM FooDocs
WHERE ConsentIdentifier = ct.Identifier
ORDER BY Version DESC) ca
答案2
得分: 1
使用CROSS APPLY是可以的，但它会在您的表中的每一行上实际调用子查询，然后再通过使用DISTINCT来去重结果，导致半笛卡尔积/三角连接。
通常，更高效的方法是仅使用ROW_NUMBER()，并避免隐式连接...
WITH
sorted_by_version AS
(
SELECT
*,
ROW_NUMBER()
OVER (
PARTITION BY ConsentTemplateIdentifier
ORDER BY version DESC
)
AS version_ordinal
FROM
ConsentTemplates
)
SELECT
*
FROM
sorted_by_version
WHERE
version_ordinal = 1
ORDER BY
DisplayOrder
英文:
Using CROSS APPLY works, but effectively invokes the sub-query for every row in in your table, then expend effort to de-duplicate the results with DISTINCT, resulting in a semi-cartesian-product / triangular-join.
It is usually much more efficient just to use ROW_NUMBER() and avoid the implicit join all together...
WITH
sorted_by_version AS
(
SELECT
*,
ROW_NUMBER()
OVER (
PARTITION BY ConsentTemplateIdentifier
ORDER BY version DESC
)
AS version_ordinal
FROM
ConsentTemplates
)
SELECT
*
FROM
sorted_by_version
WHERE
version_ordinal = 1
ORDER BY
DisplayOrder
答案3
得分: 0
通过对Derrick的回答进行轻微修改，我成功地使用CROSS APPLY得到了我想要查看的数据。
SELECT DISTINCT ca.*
FROM ConsentTemplates fd
CROSS APPLY (SELECT TOP 1 *
FROM ConsentTemplates
WHERE ConsentTemplateIdentifier = fd.ConsentTemplateIdentifier
ORDER BY Version DESC) ca
ORDER BY DisplayOrder
英文:
With a slight modification to Derrick's answer, I was able to get the data back the way I wanted to see it using CROSS APPLY
SELECT DISTINCT ca.*
FROM ConsentTemplates fd
CROSS APPLY (SELECT TOP 1 *
FROM ConsentTemplates
WHERE ConsentTemplateIdentifier = fd.ConsentTemplateIdentifier
ORDER BY Version DESC) ca
order by DisplayOrder
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论