How to Group by One Column and Retrieve a Row with The Minimum Value of Another Column in T/Sql

Selecting rows based on minimum value of one column

You could also use top (1) with ties

select top (1) with ties a, * 
from table t
order by row_number() over (partition by a order by [Order])

How do you group by one column and retrieve a row with the minimum value of another column in T/SQL?

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.

How to get the minimum of one column and get another column without grouping?

One option is to use analytic function which "ranks" data; then fetch rows that rank as the highest. Something like this:

Sample data:

SQL> with test (month, day, c_initial, ending) as
  2    (select 'jan', 24, 0, 3 from dual union all
  3     select 'jan', 24, 1, 6 from dual union all
  4     select 'jan', 24, 2, 2 from dual union all
  5     select 'feb', 12, 0, 1 from dual union all
  6     select 'feb', 12, 1, 6 from dual union all
  7     select 'feb', 12, 2, 5 from dual
  8    ),

Query begins here:

  9  temp as
 10    (select t.*,
 11       row_number() over (partition by month, day order by c_initial) rn
 12     from test t
 13    )
 14  select month, day, c_initial, ending
 15  from temp
 16  where rn = 1;

MON        DAY  C_INITIAL     ENDING
--- ---------- ---------- ----------
feb         12          0          1
jan         24          0          3

SQL>

---

If there are ties, then consider RANK instead.

Also, initial is invalid column name so I renamed it to c_initial.

Return ID row matching the minimum value in another column

If you want the min duration and its id at the same time then the simplest way to do this is to use two window functions.

SELECT DISTINCT ServiceName, MethodName
    , MIN(Duration) OVER (PARTITION BY ServiceName, MethodName ORDER BY Duration ASC)
    , FIRST_VALUE(Id) OVER (PARTITION BY ServiceName, MethodName ORDER BY Duration ASC)
FROM log

It may seem unnecessary to have the ORDER BY clause for the MIN of duration, however by reusing that partition both functions can be processed simultaneously and not have to be divided into separate sets and recombined. The best way to understand is to take the order by out and view the query plan and see how it adds Nested Loops and a lot of other extra steps. Long story short, this ends up producing a pretty short and efficient plan.

I hope it's obvious how the correct Id is retrieved. Basically this relies on the fact that sorting a set results in any value in the first row to be related to the min/max value(s) used in the sort.

If multiple Id's match the duration and you wanted to see them all, you could do the following instead. You can use TOP to limit the result to a certain number of rows.

SELECT l1.ServiceName, l1.MethodName, l1.Duration, x.Id 
FROM (
    SELECT ServiceName, MethodName, MIN(Duration) Duration 
    FROM log GROUP BY ServiceName, MethodName
) l1 
CROSS APPLY (
    SELECT TOP 10 Id 
    FROM log l2 WHERE l2.ServiceName = l1.ServiceName 
        AND l2.MethodName = l1.MethodName 
        AND l2.Duration = l1.Duration
) x

Select Rows with min value for each group

But the best would be save dates as date column. The you can use all function for dates

CREATE TABLE table1 (
  [Date]  varchar(10),
  [Container ID] INTEGER
);

INSERT INTO table1
  ([Date], [Container ID])
VALUES
  ('1/1', '1'),
  ('2/2', '1'),
  ('3/3', '1'),
  ('4/4', '2'),
  ('5/5', '2'),
  ('6/6', '3'),
  ('7/7', '3');
GO

SELECT MIN([Date]), [Container ID] FROM table1 GROUP BY [Container ID]
GO

(No column name) | Container ID
:--------------- | -----------:
1/1              |            1
4/4              |            2
6/6              |            3

db<>fiddle here

get id of min value, grouped by another column

Think of this as filtering, not aggregation. I would do:

select t.*
from t
where t.price = (select min(t2.price)
                 from t t2
                 where t2.date = t.date
                );

This has the advantage that it can make use of an index on (date, price).

If there are duplicate minimum prices on a given date, this will retrieve multiple rows.

One way to handle duplicates is to return them as a list:

select t.date, min(t.price), group_concat(t.id) as ids
from t
where t.price = (select min(t2.price)
                 from t t2
                 where t2.date = t.date
                )
group by date;

SQL group by: select value where another column has its min/max

Do the aggregation in a subquery, then look up the ID for each group in another subquery:

SELECT
  (SELECT TOP(1) id FROM MyTable WHERE grp = agg.grp ORDER BY ts DESC) AS id,
  min_ts, max_ts, grp
FROM (SELECT min(ts) AS min_ts, max(ts) AS max_ts, grp
      FROM MyTable
      GROUP BY grp) agg

Or use window functions:

SELECT id, min_ts, max_ts, grp
FROM (SELECT 
        id,
        min(ts) OVER (PARTITION BY grp) min_ts,
        max(ts) OVER (PARTITION BY grp) max_ts,
        grp,
        row_number OVER (PARTITION BY grp ORDER BY ts) rn
      FROM MyTable)
WHERE rn = 1;

This query uses window functions to calculate min_ts and max_ts for each group, and then filters to only include the first row for each group (ordered by ts).

sql select min, return value from different column in same row with grouping

We can get row number based on the date for each [Name] and pick the least date record.

SELECT [T].* 
FROM (  
    SELECT [Name]
         , [DATE]
         , [OrderType]
         , ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Date]) AS [seq]
    FROM [TableA]
) AS [T]
WHERE [T].[seq] = 1

Group by minimum value in one field while selecting distinct rows

How about something like:

SELECT mt.*     
FROM MyTable mt INNER JOIN
    (
        SELECT id, MIN(record_date) AS MinDate
        FROM MyTable
        GROUP BY id
    ) t ON mt.id = t.id AND mt.record_date = t.MinDate

This gets the minimum date per ID, and then gets the values based on those values. The only time you would have duplicates is if there are duplicate minimum record_dates for the same ID.

Getting the row with the smallest column value if another column is the same in SQL Server

You could use the rank window function:

SELECT id, number, value
FROM   (SELECT id, number, value, RANK() OVER(PARTITION BY number ORDER BY value) AS rk
        FROM   mytable)
WHERE  rk = 1

EDIT:

As noted in the comments, if there is more than one line with the lowest value, using rank would return both. If you want to return just one of them, you should use row_number instead:

SELECT id, number, value
FROM   (SELECT id, number, value,
               ROW_NUMBER() OVER(PARTITION BY number ORDER BY value) AS rn
        FROM   mytable)
WHERE  rn = 1

---

Related Topics