从每个子集中选择最大

本文介绍了从每个子集中选择最大的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在这里撞头.我觉得自己很愚蠢，因为我敢肯定我以前做过这样的事情，但是我一生都记不住怎么做.我猜那是其中的一天之一.<

I'm banging my head here. I feel pretty stupid because I'm sure I've done something like this before, but can't for the life of me remember how. One of those days I guess >.<

说我有以下数据:--->和返回此查询的查询:--->但是我想要这个:

Say I have the following data: ---> and a query which returns this: ---> But I want this:

ID FirstID ID FirstID ID FirstID -- ------- -- ------- -- ------- 1 1 1 1 7 1 2 1 3 3 3 3 3 3 4 4 6 4 4 4 5 5 5 5 5 5 6 4 7 1

请注意，我的查询返回了ID = FirstID的记录，但我希望它返回唯一FirstID的每个子集的Max(ID).听起来很简单吧?这就是我的想法，但我一直保持着仅记录#7的状态.这是我的查询(返回上面第二图的查询)，其中包含一些测试代码，以简化您的生活.我需要这样做，以便在最右边的块中给出结果.应当注意，这是一个自联接表，其中FirstID是ID的外键.谢谢:)

Notice that my query returns the records where ID = FirstID, but I want it to return the Max(ID) for each subset of unique FirstID. Sounds simple enough right? That's what I thought, but I keep getting back just record #7. Here's my query (the one that returns the second block of figures above) with some test code to make your life easier. I need this to give me the results in the far right block. It should be noted that this is a self-joining table where FirstID is a foreign key to ID. Thanks :)

declare @MyTable table (ID int, FirstID int) insert into @MyTable values (1,1),(2,1),(3,3),(4,4),(5,5),(6,4),(7,1) select ID, FirstID from @MyTable where ID = FirstID

推荐答案

这项工作

declare @MyTable table (ID int, FirstID int) insert into @MyTable values (1,1),(2,1),(3,3),(4,4),(5,5),(6,4),(7,1) Select FirstID, Max (Id) ID From @MyTable Group BY FirstID

结果

FirstID ID ----------- ----------- 1 7 3 3 4 6 5 5