CODEWITHSUNDEEP
sqlsql-server
wallenpb
wallenpb

Reputation: 77

SQL getting a minimum value from an aggregate function

I am trying to get a minimum value to return from an agregate function. I have created a query that executes, but fails to return only the one minimum row for each group. In this example, there are multiple rows for each prod_num, each with a unique serial number. This is for SQL Server.

My code:

SELECT 
    x.prod_num, Min(x.sn) sn, y.qty, x.date_tested,x.scrap
FROM 
    serial_numbers x
JOIN 
    prod_ord y on y.prod_num = x.prod_num
WHERE
    x.date_tested IS NOT NULL 
    AND x.scrap = 1
GROUP BY 
    x.prod_num, x.sn, y.qty, x.date_tested, x.scrap
ORDER BY 
    x.date_tested desc, x.prod_num

My results:

prod_num    sn            qty   date_tested           scrap
------------------------------------------------------------
301336662   120214A10338    7   2014-12-09 19:26:41.650 1
301336662   120214A10339    7   2014-12-09 19:26:41.650 1
301336662   120214A10340    7   2014-12-09 19:26:41.650 1
301336662   120214A10341    7   2014-12-09 19:26:41.650 1
301336662   120214A10342    7   2014-12-09 19:26:41.650 1
301336662   120214A10343    7   2014-12-09 19:26:41.650 1
301336662   120214A10344    7   2014-12-09 19:26:41.650 1
301303464   101014A11003    4   2014-10-16 15:18:06.817 1
301303464   101014A11004    4   2014-10-16 15:18:06.817 1
301303464   101014A11005    4   2014-10-16 15:18:06.817 1
301303464   101014A11006    4   2014-10-16 15:18:06.817 1
301293879   100714A10258    15  2014-10-13 13:23:58.923 1
301293879   100714A10259    15  2014-10-13 13:23:58.923 1
301293879   100714A10260    15  2014-10-13 13:23:58.923 1
301293879   100714A10261    15  2014-10-13 13:23:58.923 1
301293879   100714A10262    15  2014-10-13 13:23:58.923 1
301293879   100714A10263    15  2014-10-13 13:23:58.923 1
301293879   100714A10264    15  2014-10-13 13:23:58.923 1
301293879   100714A10265    15  2014-10-13 13:23:58.923 1
301293879   100714A10266    15  2014-10-13 13:23:58.923 1

What I am really trying to get is only one row for each prod_num, the row with the smallest sn.

Like this.

prod_num    sn            qty   date_tested           scrap
------------------------------------------------------------
301336662   120214A10338    7   2014-12-09 19:26:41.650 1
301303464   101014A11003    4   2014-10-16 15:18:06.817 1
301293879   100714A10258    15  2014-10-13 13:23:58.923 1

Can someone please point me to where I am going wrong?

Thanks, Bill

Upvotes: 1

Views: 100

Answers (1)

xQbert
xQbert

Reputation: 35323

Remove x.sn from the group by...

Your statement of: 

SELECT x.prod_num, Min(x.sn) sn, y.qty, x.date_tested,x.scrap
FROM serial_numbers x
JOIN prod_ord y on y.prod_num = x.prod_num
where x.date_tested is not NULL and x.scrap = 1
group by x.prod_num, x.sn, y.qty, x.date_tested,x.scrap
order by x.date_tested desc, x.prod_num

Should be: 

SELECT x.prod_num, Min(x.sn) sn, y.qty, x.date_tested,x.scrap
FROM serial_numbers x
JOIN prod_ord y on y.prod_num = x.prod_num
where x.date_tested is not NULL and x.scrap = 1
group by x.prod_num, y.qty, x.date_tested,x.scrap
order by x.date_tested desc, x.prod_num

You're telling the engine to group by x.sn which it's doing, but you only want the minimum one. By removing the x.sn from the group by, you should get your desired result.

Upvotes: 3

Related Questions