CODEWITHSUNDEEP
sqlsql-servergroupingaggregate-functions
Neville Harrison
Neville Harrison

Reputation: 1

Grouping by one column and finding minimum from another

I'm struggling with how I could use SQL aggregate functions and grouping to find the right record in my table. Below is a simplified example. I want to group by B and find the record with the minimum value of C. My problem being that I must either include A in the grouping or apply an aggregate function to A, none of which seem suitable.

A   |  B  |   C
7   |  2  |   1
4   |  2  |   2

These are simplified versions of the two queries I've tried.

select A, B, min(c)
from @Table1
group by B, A

select min(a), B, min(c)
from @Table1
group by B

The query would need to return this:

A   |  B  |   C
7   |  2  |   1

And ideally only one record for multiple values of C.

Upvotes: 0

Views: 79

Answers (7)

Shanaka Madusanka
Shanaka Madusanka

Reputation: 1

You can write the answer for your problem like this.

select A, B,MIN(C) As a from Table where B in ( SELECT Max(B) FROM Table GROUP BY B )

Upvotes: 0

Abdul Samad
Abdul Samad

Reputation: 39

i think this is you need 

 select max(a),min(b),min(c) from table

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Per B you want the record with the minimum C. This can be achieved with ROW_NUMBER:

select a, b, c
from
(
  select a, b, c, row_number() over (partition by b order by c) as rn
  from @Table1
) ranked
where rn = 1;

If there are ties (same B, same C) one of the records is picked arbitrarily. If you want a certain one (e.g. least or greatest A), then extend the ORDER BY clause. If you want all best ranked records in case of a tie, use RANK or DENSE_RANK instead of ROW_NUMBER.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You want the records for which not exists another record with the same B and a lower C.

select *
from @Table1 t1
where not exists
(
  select *
  from @Table1 better
  where better.b = t1.b
    and better.c < t1.c
);

If there are ties (same B, same C) you will get multiple records per B. If you only want only one of them, then extend the WHERE clause if possible or choose another solution.

Upvotes: 0

mohan111
mohan111

Reputation: 8865

Declare @Table1 TABLE 
    (A int, B int, C int)
;

INSERT INTO @Table1
    (A, B, C)
VALUES
    (7, 2, 1),
    (4, 2, 2)
;

Script : 

Select A.A,A.B,AA.C from 

(select B,MAX(A)A from @Table1
GROUP BY B)A
INNER JOIN (
select B,MIN(C)C from @Table1
GROUP BY B)AA
ON A.B = AA.B

Upvotes: 0

Peter
Peter

Reputation: 850

If you want each record with the min C you can use the query below. If you want one record per B you can use the answer Thorsten provided.

Declare @myTable table (A int, B int, c int)
insert into @myTable values (7,2,1),(4,2,2),(6,2,1)

select a,b,c
from(
        select *, min(c)over(partition by B) minc
        from @myTable
        ) D
where c=minC

Upvotes: 0

Esteban P.
Esteban P.

Reputation: 2809

Should also bring your desired result:

SELECT mt.*
 FROM mytable mt
 INNER JOIN 
    (
    SELECT B, MIN(C) as C
      FROM mytable
     GROUP BY B
    ) min
ON mt.B = min.B
AND mt.C = min.C

Upvotes: 0

Related Questions