本文是小编为大家收集整理的关于SQL查询。只显示每组中最新的Id的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
我有以下 SQL 表:
Name Description Id UserId CreatedDate UserSet1 Desc1 1 Abc 06/01/2018 UserSet1 Desc2 2 Def 06/02/2018 UserSet2 Desc for 2 5 NewUser 06/04/2018 UserSet2 Desc for 2 7 NewUser 06/19/2018
我想从上表中提取的只是每个 Name 的最新 Id，以便我可以得到以下输出
Name Description Id UserId CreatedDate UserSet1 Desc2 2 Def 06/01/2018 UserSet2 Desc for 2 7 NewUser 06/19/2018
自从Id 2 &7 是表中 UserSet1 & 的最新条目.UserSet2，我想显示它而不是表中的所有条目.
任何输入我怎样才能得到想要的结果.
我对直接返回输出的解决方案或任何 linq (C#) 解决方案持开放态度.即返回整个数据集，然后使用 linq 过滤上述内容.
推荐答案
编辑:由于您正在寻找最高编号的 ID，GROUP BY 方法可能更容易使用.
使用窗口函数:
SELECT *
FROM (
SELECT Name, Description, Id, UserId, CreatedDate
, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY CreatedDate DESC) AS rn
FROM myTable
) s1
WHERE rn = 1
我没有要测试的 dynamoDB 实例，但我相信它可以使用 ROW_NUMBER() 窗口函数.
其他推荐答案
感谢大家指出正确的方向.我已经使用下面的 Linq 和 C# 代码完成了这个工作:
var results = response.GroupBy(row => row.Name)
.SelectMany(g => g.OrderByDescending(row => row.Id).Take(1));
对于初始测试，这似乎有效.如果您认为这有问题，请告诉我.
其他推荐答案
这应该是一个通用的SQL答案:
SELECT * FROM yourtable Y1
WHERE Id = (SELECT MAX(Id)
FROM yourtable Y2
WHERE Y2.Name = Y1.Name)
问题描述
I have the following SQL Table:
Name Description Id UserId CreatedDate UserSet1 Desc1 1 Abc 06/01/2018 UserSet1 Desc2 2 Def 06/02/2018 UserSet2 Desc for 2 5 NewUser 06/04/2018 UserSet2 Desc for 2 7 NewUser 06/19/2018
What I want to extract from the above table is just the latest Id for each Name so that I could get the following output
Name Description Id UserId CreatedDate UserSet1 Desc2 2 Def 06/01/2018 UserSet2 Desc for 2 7 NewUser 06/19/2018
Since Id 2 & 7 are the latest entries in the table for UserSet1 & UserSet2, I would like to display that instead of all the entries in the table.
Any inputs how can I get the desired result.
I am open for solutions directly returning the output or any linq (C#) solutions as well. Ie returning the entire dataset and then using linq to filter the above.
推荐答案
EDIT: Since you are looking for the highest number ID, the GROUP BY method would probably be easier to work with.
Using a window function:
SELECT *
FROM (
SELECT Name, Description, Id, UserId, CreatedDate
, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY CreatedDate DESC) AS rn
FROM myTable
) s1
WHERE rn = 1
I don't have an instance of dynamoDB to test on, but I believe it can use the ROW_NUMBER() window function.
其他推荐答案
Thanks everyone for pointing to right direction. I have got this working with the below code of Linq and C#:
var results = response.GroupBy(row => row.Name)
.SelectMany(g => g.OrderByDescending(row => row.Id).Take(1));
For the initial tests this seems to be working. Let me know if you think this has come issues.
其他推荐答案
This should be a general SQL answer:
SELECT * FROM yourtable Y1
WHERE Id = (SELECT MAX(Id)
FROM yourtable Y2
WHERE Y2.Name = Y1.Name)
更多相关问答
联系站长