A selection with the lowest id [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

This is my thought...

I'm making a selection from a database and one of the columns is oe.prodplan_id. This one contains an number that referes to another table. Many of the selected rows contains the same number in oe.prodplan_id so i just want all the rows that contains the lowest value. For exampel all rows where oe.prodplan_id = 1. The thing is that i dont now the lowest value, so from the selected rows i only want thoses with lowest number.. Maybe my explanation is bad, but so is my english :)

This is my sql question if it helps:

SELECT 
    oe.id AS oeID, oh.ordernumber, oe.order_line, oe.prod_week, 
    oe.prodplan_id, oe.door_type, oe.amount AS amountOfDoors, oe.inner_color, 
    oe.outer_color, oe.prod_week, a.name, a.description, i.amount AS iAmount, 
    md.status AS mdStatus, i.width, i.length, c.color_name AS innerColor, 
    c2.color_name AS outerColor, pp.status, oe.pos, d.name, md.id AS mdid 
FROM pd_mounting_details AS md 
LEFT OUTER JOIN pd_order_eco A开发者_如何学JAVAS oe ON md.order_data = oe.id 
LEFT OUTER JOIN pd_article AS a ON md.article = a.id 
LEFT OUTER JOIN pd_inventory AS i ON md.id = i.pp_id 
LEFT OUTER JOIN pd_order_head AS oh ON oe.order_head = oh.id 
LEFT OUTER JOIN pd_colors AS c ON oe.inner_color = c.id 
LEFT OUTER JOIN pd_colors AS c2 ON oe.outer_color = c2.id 
LEFT OUTER JOIN pd_doors as d ON oe.door=d.id 
LEFT OUTER JOIN pd_production_plan AS pp ON oe.prodplan_id = pp.id 
WHERE 
     (a.production_group = 4) 
     AND (NOT (oe.amount = 0)) 
     AND (pp.status = 4) 
     AND md.status = 4
     AND (startTime = '' OR startTime IS NULL) 
     AND (stopTime = '' OR stopTime IS NULL)
ORDER BY oe.pos

Best regards

---

SELECT 
    oe.id AS oeID, oh.ordernumber, oe.order_line, oe.prod_week, 
    oe.prodplan_id, oe.door_type, oe.amount AS amountOfDoors, oe.inner_color, 
    oe.outer_color, oe.prod_week, a.name, a.description, i.amount AS iAmount, 
    md.status AS mdStatus, i.width, i.length, c.color_name AS innerColor, 
    c2.color_name AS outerColor, pp.status, oe.pos, d.name, md.id AS mdid 
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 
LEFT OUTER JOIN pd_inventory AS i ON md.id = i.pp_id 
LEFT OUTER JOIN pd_order_head AS oh ON oe.order_head = oh.id 
LEFT OUTER JOIN pd_colors AS c ON oe.inner_color = c.id 
LEFT OUTER JOIN pd_colors AS c2 ON oe.outer_color = c2.id 
LEFT OUTER JOIN pd_doors as d ON oe.door=d.id 
LEFT OUTER JOIN pd_production_plan AS pp ON oe.prodplan_id = pp.id 
WHERE 
     (a.production_group = 4) 
     AND (NOT (oe.amount = 0)) 
     AND (pp.status = 4) 
     AND md.status = 4
     AND (startTime = '' OR startTime IS NULL) 
     AND (stopTime = '' OR stopTime IS NULL)

     --ADDED
     AND oe.prodplan_id = (SELECT MIN(prodplan_id) FROM pd_order_eco)

ORDER BY oe.pos

---

You use a MIN() function. Because that is an 'aggregate' function, you have to include the other fields in a GROUP BY clause. For example:

SELECT
   MIN(prodplan.id),
   oh.ordernumber
FROM
   ...
WHERE
   ...
GROUP BY
   oh.ordernumber

Raw result set (based on all of your table joins, filters, etc.):

prodplan.id | oh.ordernumber | funny_word
1           | 1              | cow
2           | 1              | cow
3           | 1              | tree

Aggregated result set (Using MIN on prodplan.id)

1           | 1              | cow
3           | 1              | tree

It filtered out this record (because everything besides prodplan.id was the same, and the prodplan.id for the record it kept was the minimum number of the two)

2           | 1              | cow