按组的最小值在SQL Server 2008 R2中进行分组(Grouping in SQL Server 2008 R2 by min value of the group)

编程入门行业动态更新时间:2024-10-17 07:38:48

我在sql中运行这个命令

select ID, TASK, CreatedBy,CreateDate from John Results group by ID, TASK, CreatedBy, CreateDate

这是返回这个

20 text user1 2008-11-07 19:10:47.230 20 text user2 2008-11-07 17:45:17.993 24 text user3 2008-11-07 18:58:08.727 25 text user4 2008-11-07 20:19:01.293 36 text user5 2008-11-10 21:31:23.610 37 text user6 2008-11-10 21:34:09.597 38 text user7 2008-11-10 21:35:19.660

有两个ID是20，我试图用最低的日期得到结果，所以在这个例子中它会是

20 text user2 2008-11-07 17:45:17.993

我将如何去做所有重复的ID 非常感谢你

I am running this command in sql

select ID, TASK, CreatedBy,CreateDate from John Results group by ID, TASK, CreatedBy, CreateDate

which is returning this

There are two ID's that are 20 and I am trying to get the result with the lowest date so in this example it would be

20 text user2 2008-11-07 17:45:17.993

How would I go about doing that for all the duplicate ID's Thank you very much

最满意答案

WITH mycte (id, createdate, createdby, rownum) AS ( SELECT id, createdate,createdby, ROW_NUMBER() OVER (PARTITION BY id ORDER BY createdate ASC) AS ROWNUM FROM johnresults ) SELECT j.id, j.task, mycte.createdby, mycte.createdate FROM johnresults j inner join mycte ON j.id = mycte.id and mycte.rownum = 1 GROUP BY j.id, j.task, mycte.createdby, mycte.createdate WITH mycte (id, createdate, createdby, rownum) AS ( SELECT id, createdate,createdby, ROW_NUMBER() OVER (PARTITION BY id ORDER BY createdate ASC) AS ROWNUM FROM johnresults ) SELECT j.id, j.task, mycte.createdby, mycte.createdate FROM johnresults j inner join mycte ON j.id = mycte.id and mycte.rownum = 1 GROUP BY j.id, j.task, mycte.createdby, mycte.createdate

更多推荐

本文发布于:2023-07-16 12:53:00，感谢您对本站的认可！

本文链接:https://www.elefans.com/category/jswz/34/1128652.html