views:

588

answers:

5

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:

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:

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.

What am I doing wrong?

+1  A: 

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"

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.

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):

...
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.

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.

butterchicken
+1 for linking rather than throwing some SQL together as quick as one can...
gbn
Cheeky. Editing while I comment.
gbn
@gbn I was "adding value" to my answer! :)
butterchicken
Hey, I appreciate the added value!! :)
kronoz
Bear in mind 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
Hm, I wasn't aware of those caveats; @butterchicken - perhaps worth adding them to the solution so other users are aware?
kronoz
Incidentally, I am happy for the solution to return multiple rows when the minimum value of foo is duplicated and for nullls to be included, however it's important users who may not be happy with that are made aware.
kronoz
Thanks Adam; I neglected to mention the dupes, and plain forgot about `null`s! Updated answer.
butterchicken
Nice one @butterchicken :-)
kronoz
+1  A: 

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.

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.

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 :)

Blorgbeard
+1  A: 

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
astander
+2  A: 
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.

Adam Robinson
Worth mentioning this doesn't work prior to MSSQL2005 when row_number() was introduced.
butterchicken
+1  A: 

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.

HAdes