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.