Duplicates in Aggregate & Min Query

I have a query . . .that works perfectly (before I add RESOURCE_ID), but when I add resource, I get duplicates.

SELECT        WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, MIN(SEQUENCE_NO) AS Expr1, RESOURCE_ID
FROM            OPERATION
GROUP BY WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, STATUS, RESOURCE_ID
HAVING        (WORKORDER_TYPE = 'W') AND (STATUS = 'R')
ORDER BY WORKORDER_BASE_ID

I would like to add Resource_ID. . .but resource may be duplicated . . I don't want duplicates for Resource. . I would think I need to put a query in a query?

WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, SEQUENCE_NO, RESOURCE_ID
100, 1, 1, 10, C, ISSUE
100, 1, 1, 20, R, ASSEMBLE
100, 1, 1, 30, R, MACHINE
100, 1, 1, 40, R, MACHINE

I would like to see this, please. . .but I'm missing something

WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, SEQUENCE_NO, RESOURCE_ID
100, 1, 1, 20, R, ASSEMBLE

My query results are this. . .
WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, SEQUENCE_NO, RESOURCE_ID
100, 1, 1, 20, R, ASSEMBLE
100, 1, 1, 30, R, MACHINE
100, 1, 1, 40, R, MACHINE
CadenceAerospaceITAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
awking00Connect With a Mentor Commented:
You need to define the criteria for which resource_id to select (i.e. lowest sequence_no  or by alphabetical order where status = 'R').
0
 
Louis01Commented:
Assuming you want to return the RESOURCE_ID of the same row as MIN(SEQUENCE_NO) and that SEQUENCE_NO is unique in each row for WORKORDER_BASE_ID, you can try the following
SELECT t1.WORKORDER_BASE_ID, t1.WORKORDER_LOT_ID, t1.WORKORDER_SPLIT_ID, t1.MIN_SEQUENCE_NO, t2.RESOURCE_ID
  FROM (SELECT WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, MIN(SEQUENCE_NO) AS MIN_SEQUENCE_NO
          FROM OPERATION
         GROUP BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID
        HAVING WORKORDER_TYPE = 'W'
           AND STATUS = 'R') t1
        INNER JOIN (SELECT WORKORDER_BASE_ID, SEQUENCE_NO, RESOURCE_ID FROM OPERATION) t2
    ON t1.WORKORDER_BASE_ID = t2.WORKORDER_BASE_ID
   AND t1.MIN_SEQUENCE_NO = t2.SEQUENCE_NO
 ORDER BY t1.WORKORDER_BASE_ID

Open in new window

0
All Courses

From novice to tech pro — start learning today.