如何按一列分组并检索T / SQL中另一列的最小值的行？

So I know this is a pretty dumb question, however (as the rather lengthily title says) I would like to know how do the following:

所以我知道这是一个非常愚蠢的问题，但是（正如相当冗长的标题所说）我想知道如何做到以下几点：

I have a table like this:

我有这样一张桌子：

ID Foo Bar Blagh
----------------
1  10  20  30
2  10  5   1
3  20  50  40
4  20  75  12

I want to group by Foo, then pull out rows with minimum Bar, i.e. I want the following:

我想按Foo分组，然后用最小的Bar拉出行，即我想要以下内容：

ID Foo Bar Blagh
----------------
2  10  5   1
3  20  50  40

I can't for the life of me work out the correct SQL to retrieve this. I want something like:

我不能为我的生活找出正确的SQL来检索这个。我想要的东西：

SELECT ID, Foo, Bar, Blagh
FROM Table
GROUP BY Foo
HAVING(MIN(Bar))

However this clearly doesn't work as that is completely invalid HAVING syntax and ID, Foo, Bar and Blagh are not aggregated.

然而，这显然不起作用，因为它完全无效HAVING语法和ID，Foo，Bar和Blagh没有聚合。

What am I doing wrong?

我究竟做错了什么？

6 个解决方案

#1

This is almost exactly the same question, but it has some answers!

这几乎是完全相同的问题，但它有一些答案！

Here's me mocking up your table:

这是我嘲笑你的桌子：

declare @Borg table (
    ID int,
    Foo int,
    Bar int,
    Blagh int
)
insert into @Borg values (1,10,20,30)
insert into @Borg values (2,10,5,1)
insert into @Borg values (3,20,50,70)
insert into @Borg values (4,20,75,12)

Then you can do an anonymous inner join to get the data you want.

然后，您可以执行匿名内部联接以获取所需的数据。

select B.* from @Borg B inner join 
(
    select Foo,
        MIN(Bar) MinBar 
    from @Borg 
    group by Foo
) FO
on FO.Foo = B.Foo and FO.MinBar = B.Bar

EDIT Adam Robinson has helpfully pointed out that "this solution has the potential to return multiple rows when the minimum value of Bar is duplicated, and eliminates any value of foo where bar is null"

编辑Adam Robinson帮助指出“当Bar的最小值重复时，此解决方案有可能返回多行，并消除bar为null的任何foo值”

Depending upon your usecase, duplicate values where Bar is duplicated might be valid - if you wanted to find all values in Borg where Bar was minimal, then having both results seems the way to go.

根据您的用例，重复Bar的重复值可能是有效的 - 如果您想在Borg中找到Bar最小的所有值，那么两个结果似乎都是可行的。

If you need to capture NULLs in the field across which you are aggregating (by MIN in this case), then you could coalesce the NULL with an acceptably high (or low) value (this is a hack):

如果你需要在聚合的字段中捕获NULL（在这种情况下是MIN），那么你可以用一个可接受的高（或低）值来合并NULL（这是一个hack）：

...
MIN(coalesce(Bar,1000000)) MinBar -- A suitably high value if you want to exclude this row, make it suitably low to include
...

Or you could go for a UNION and attach all such values to the bottom of your resultset.

或者你可以去一个UNION并将所有这些值附加到结果集的底部。

on FO.Foo = B.Foo and FO.MinBar = B.Bar
union select * from @Borg where Bar is NULL

The latter will not group values in @Borg with the same Foo value because it doesn't know how to select between them.

后者不会将@Borg中的值与Foo值相同，因为它不知道如何在它们之间进行选择。

#2

select 
    ID, 
    Foo, 
    Bar, 
    Blagh
from Table
join (
    select 
    ID, 
    (row_number() over (order by foo, bar) - rank() over (order by foo)) as rowNumber
) t on t.ID = Table.ID and t.rowNumber = 0

This joins on the table again, but this time adds a relative row number for the value for bar, as if it were sorted ascending within each value of foo. By filtering on rowNumber = 0, it selects only the lowest values for bar for each value of foo. This also effectively eliminates the group by clause, since you're now only retrieving one row per foo.

这再次加入到表中，但这次为bar的值添加了一个相对行号，就好像它在foo的每个值中按升序排序一样。通过对rowNumber = 0进行过滤，它仅为foo的每个值选择bar的最低值。这也有效地消除了group by子句，因为你现在每个foo只检索一行。

#3

My understanding is that you can't really do this in one go.

我的理解是，你不能一气呵成。

select Foo, min(Bar) from table group by Foo

,, gets you the minimum Bar for each distinct Foo. But you can't tie that minimum to a particular ID, because there could be more than one row with that Bar value.

，为每个不同的Foo提供最小的条形码。但是，您无法将该最小值与特定ID绑定，因为可能存在多个具有该Bar值的行。

What you can do is something like this:

你能做的是这样的：

select * from Table t
join (
   select Foo, min(Bar) as minbar
   from Table group by Foo
) tt on t.Foo=tt.Foo and t.Bar=tt.minbar

Note that if there is more than one row with the minimum Bar value, you'll get them all with the above query.

请注意，如果有多个Bar值最小的行，您将使用上述查询获取所有这些行。

Now, I am not claiming to be a SQL guru, and it is late where I am, and I may be missing something, but there's my $0.02 :)

现在，我并不是自称是一个SQL大师，现在已经很晚了，而且我可能会遗漏一些东西，但是我的价格是0.02美元:)

#4

This might help:

这可能有所帮助：

DECLARE @Table TABLE(
        ID INT,
        Foo FLOAT,
        Bar FLOAT,
        Blah FLOAT
)

INSERT INTO @Table (ID,Foo,Bar,Blah) SELECT 1, 10 ,20 ,30
INSERT INTO @Table (ID,Foo,Bar,Blah) SELECT 2, 10 ,5 ,1
INSERT INTO @Table (ID,Foo,Bar,Blah) SELECT 3, 20 ,50 ,40
INSERT INTO @Table (ID,Foo,Bar,Blah) SELECT 4, 20 ,75 ,12

SELECT  t.*
FROM    @Table t INNER JOIN
        (
            SELECT  Foo,
                    MIN(Bar) MINBar
            FROM    @Table
            GROUP BY Foo
        ) Mins ON t.Foo = Mins.Foo
                AND t.Bar = Mins.MINBar

#5

Another option would be something along the lines of the following:

另一种选择可能是以下几点：

DECLARE @TEST TABLE(    ID int,    Foo int,    Bar int,    Blagh int)
INSERT INTO @TEST VALUES (1,10,20,30)
INSERT INTO @TEST VALUES (2,10,5,1)
INSERT INTO @TEST VALUES (3,20,50,70)
INSERT INTO @TEST VALUES (4,20,75,12)

SELECT Id, Foo, Bar, Blagh 
FROM (
      SELECT id, Foo, Bar, Blagh, CASE WHEN (Min(Bar) OVER(PARTITION BY FOO) = Bar) THEN 1 ELSE 0 END as MyRow
      FROM @TEST) t
WHERE MyRow = 1

Although this still requires a sub-query it does eliminate the need for joins.

虽然这仍然需要子查询，但它确实消除了对连接的需要。

Just another option.

只是另一种选择。

#6

declare @Borg table (
    ID int,
    Foo int,
    Bar int,
    Blagh int
)
insert into @Borg values (1,10,20,30)
insert into @Borg values (2,10,5,1)
insert into @Borg values (3,20,50,70)
insert into @Borg values (4,20,75,12)

select * from @Borg

select Foo,Bar,Blagh from @Borg b 
where Bar = (select MIN(Bar) from @Borg c where c.Foo = b.Foo)

#1