从每个子集中选择最大 [英] Select Max from each Subset

问题描述

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

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

这篇关于从每个子集中选择最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！