Vet inte om det är möjligt men jag tänker så här..
om jag gör ett urval och i detta urval finns det en kolumn som heter prodplan_id. Nu vill jag få till så att alla rader väljs där det lägsta prodplan_id väljs..
Alltså urvalet av frågan kanske resulterar i hundra rader, utav dessa har hälften av rederna ett prodplan_id och resterande ett annat.. Nu vill jag få ut dom 50 som har det lägsta av dom två prodplan_id'ena..
Trodde man kunde använda MIN eller liknande, men fick inte ut något av det..
Frågan ser ut så här nu om det är till någon hjälp:
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) ORDER BY oe.pos
SELECT TOP 50 * FROM (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)) as drv
ORDER BY drv.prodplan_id
Enkelt och smidigt dock en extra select men borde funka? Eller har jag förstått dig fel =)
alltså, n var jag nog lite otydlig... Det var bara ett exmpel med just 50. .Jag vet aldrig hur många rader det är utan vill bara får ut alla dom rader som innehåller just det lägsta id'et?
Jaha okej.. Du kan göra som nedan exempel. Problemet är att frågan blir lite tung. Du kanske inte behöver joina in så mycket för att få ut det lägsta oe.prodplan-id:t? Och kanske du inte behöver du inte joina in så mycket för att få fram datan du vill ha när du redan vet oe.prodplan_id:t. Då blir inte frågan sååå jobbig. Jag skulle inte göra på detta vis om den kördes ofta.
Du skulle nog kunna köra en "WITH CTE" fråga också. Lite svårt när man inte har tabellerna framför sig så man kan testa bara.
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)
AND oe.prodplan_id = (SELECT MIN(oe.prodplan_id) FROM etc....joins...)
ORDER BY oe.pos
Jo men det får du om du gör som mitt exempel. Alltså alla kolumner.
Såhär fast nu är den inte "optimerad"
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)
AND oe.prodplan_id = (SELECT MIN(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
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
A klockrent.. NU fungerar det..
Men det gick korta ner till:
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)
AND oe.prodplan_id = (SELECT MIN(oe.prodplan_id) from pd_order_eco) ORDER BY oe.pos
266 ms totalt · 3 externa anrop · v20260731065814-full.a4a6ad0e
