Using MIN() to get the lowest value, but i got two rows?

I'm using a SQL question were I want to find the lowest value from the field prod_week.

This is the query:

SELECT 
     MIN(oe.prod_week), oe.prodplan_id 
FROM 
    pd_mounting_details as md 
LEFT OUTER JOIN 
    pd_order_eco AS oe ON md.order_data = oe.id 
LEFT OUTER JOIN 
    pd_article AS a ON md.article = a.id 
WHERE 
    oe.status = 4 
    AND (md.starttime = '' OR md.starttime IS NULL)
    AND (a.production_group = 4)  
    AND (NOT (oe.amount = 0)) 
GROUP BY
    oe.prodplan_id

The result of this开发者_运维百科 is

prod_week      |  prodplan_id
  1126         |     27
  1127         |     28

What I don't understand is why this result in two rows when I used MIN(prod_week) to get the row with the lowest week number.

If I remove the prodplan_id from the selection it all works and I get one row were prod_week is "1126". And from that all I want is to get the id prodplan_id to.

I hope this question isn't to blurry?

You are using GROUP BY, which means you will get one row per GROUP.

In this case your GROUP is prodplan_id and there are two matching values.

To get both values you can try:

SELECT oe.prod_week, oe.prodplan_id 
FROM pd_mounting_details as md 
LEFT OUTER JOIN pd_order_eco AS oe 
   ON md.order_data = oe.id 

WHERE oe.prod_week = (SELECT MIN(oe.prod_week)
                      FROM pd_mounting_details as md 
                      LEFT OUTER JOIN pd_order_eco AS oe 
                         ON md.order_data = oe.id
                      LEFT OUTER JOIN pd_article AS a 
                         ON md.article = a.id where oe.status=4 
                      AND (md.starttime ='' or md.starttime is null) 
                      AND (a.production_group = 4) 
                      AND (NOT (oe.amount = 0))) 

When you do

select min(x),y
from table
group by y;

what you're doing is getting y and the smallest value of x for each distinct value of y. So, since prodplan_id has values of 27 and 28 in your morass of joins, we have that the smallest value of prod_week that appears when prodplan_id=27 is 1126, and the smallest value of prod_week that appears when prodplan_id=28 is 1127.

ETA: If you want one row, you could do an order by 1 limit 1 at the end.

ETA^2: You can also wrap things up in a subquery and use a where clause at the end:

select min_prod_week,prodplan_id
from(
    select min(oe.prod_week) as min_prod_week,oe.prodplan_id
    from....
    group by oe.prodplan_id
    )min
where min_prod_week=(select min(prod_week) from pd_order_eco)

Since your select statement ends with a group by clause, you are selecting the minimum prod_week for each prodplan_id instead of the overall minimum. Remove the group by clause and it should work as you expect.