Min function in SSMS on query with multiple columns

Question

Struggling with MIN function. Need to grab other values on the row with the minimum value. Said differently, I have a parent item, a child item, and a qty. I need for a parent item, the child item and the child item's qty.

Parent  Child   Value
A   AB  1
A   BC  2
A   CD  3
B   AB  4
B   BC  5
B   EE  6
C   AB  2
C   EE  4

The query should return as result

Parent  Child   Value
A   AB  1
B   AB  4
C   EE  4

It is OK that AB is duplicated (because it is unique to parent). My 'group by' did not work, and I suspect I need to use the over by clause with possibly, partition by...

The SQL below does not work 100%. I think the Min_Qty is correct, but the Child is random, not the child affiliated with the Qty and get too many row results.

SELECT  Parent,
        Child,
        MIN(Qty) OVER (PARTITION BY Child) AS Min_Qty
FROM #Temp
GROUP BY Parent, Child, Qty

Since you seem to know about the OVER() function, why don't you just do a ROW_NUMBER() OVER(PARTITION BY Child ORDER BY...)? — Tab Alleman, Jun 28 '17 at 19:10
Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Tab Alleman, Jun 28 '17 at 19:18

score 1 · Accepted Answer · answered Jun 28 '17 at 19:24

1

As stated earlier you can use window function and assign row numbers ordered by qty. Then get all RowNum = 1.

RexTester Demo

;WITH cte AS (
SELECT 
    *
  , ROW_NUMBER() OVER (PARTITION BY Parent ORDER BY Qty ASC) AS RowNum 
FROM YourTable
)

SELECT
    Parent
  , Child
  , Qty
FROM cte WHERE rownum = 1

answered Jun 28 '17 at 19:24

SQLChao

7,709
1
17
32

I was able to adapt this and abandon the min() function. Thanks for the working alternative. I've checked it as the answer though it was not with the min() function. – Nicho247 Jun 28 '17 at 23:02

score 0 · Answer 2 · answered Jun 28 '17 at 19:31

You can also do a simple self join

declare @tmp table
(
parent char(1),
child char(2),
age smallint
);
insert into @tmp VALUES('A', 'AB', 1);
insert into @tmp VALUES('A', 'BC', 2);
insert into @tmp VALUES('A', 'CD', 3);
insert into @tmp VALUES('B', 'AB', 4);
insert into @tmp VALUES('B', 'BC', 5);
insert into @tmp VALUES('B', 'EE', 6);
insert into @tmp VALUES('C', 'AB', 6);
insert into @tmp VALUES('C', 'EE', 4);

SELECT t.parent, t.child, t.age from @tmp t INNER JOIN 
(SELECT parent, Min(age) as MinAge from @tmp GROUP BY parent)
g on g.parent = t.parent and g.minage = t.age

Note I have changed the data of your c so that EE is the one selected. With your data you get AB for C as well!

Min function in SSMS on query with multiple columns

2 Answers2