SQL查询。只显示每组中最新的Id[英] SQL Query: Display only latest Id from each set

本文是小编为大家收集整理的关于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)

本文地址:https://www.itbaoku.cn/post/1556988.html

问题描述

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)