Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you group by one column and retrieve a row with the minimum value of another column in 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:

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?

like image 472
ljs Avatar asked Jul 31 '09 11:07

ljs


People also ask

How do I select a row with minimum value in SQL?

To select data where a field has min value, you can use aggregate function min(). The syntax is as follows. SELECT *FROM yourTableName WHERE yourColumnName=(SELECT MIN(yourColumnName) FROM yourTableName); To understand the above syntax, let us create a table.

How do you find the minimum value between two columns in SQL?

you can find a row-wise minimum like this: SELECT C1, C2, C3, ( SELECT MIN(C) FROM (VALUES (C1), (C2), (C3) AS v (C) ) AS MinC FROM T ; Basically you are arranging the values of C1 , C2 , C3 as a column and are applying a normal (column-wise) aggregate function to it to find the minimum.

How do you select the first row of each unique value of a column?

Generally, here is how to select only the first row for each unique value of a column: Navigate to the Data tab and tap on Advanced under Sort & Filter. In the pop-up catalog, include the list range as the first column and check Unique Records Only. Click OK to select the first row with unique values.

How do you select the first value in a GROUP BY a bunch of rows?

To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ). You assign the row numbers within each group (i.e., year).


1 Answers

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.

like image 145
butterchicken Avatar answered Sep 23 '22 11:09

butterchicken