Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014

    Unanswered: Top N Per Group HELP!!!

    SELECT COMPLAINT_qry_containment_WIP.Plan_Date, COMPLAINT_qry_containment_WIP.Tool_Number, COMPLAINT_qry_containment_WIP.Works_Order_Number, COMPLAINT_qry_containment_WIP.Job_Number, COMPLAINT_qry_containment_WIP.Customer_Code, COMPLAINT_qry_containment_WIP.Full_Name, COMPLAINT_qry_containment_WIP.Route_Status, COMPLAINT_qry_containment_WIP.Manufacturing_Site, COMPLAINT_qry_containment_WIP.Route_Operation_Name , COMPLAINT_qry_containment_WIP.BtrieveDate, COMPLAINT_qry_containment_WIP.Route_List_Code, COMPLAINT_qry_containment_WIP.Operstat_Index_No
    FROM COMPLAINT_qry_containment_WIP
    WHERE (((COMPLAINT_qry_containment_WIP.Operstat_Index_No ) In (Select Top 1 [COMPLAINT_qry_containment_WIP].[Operstat_Index_No] from [COMPLAINT_qry_containment_WIP] where [Works_Order_Number] Order By [COMPLAINT_qry_containment_WIP].[Route_List_Code] asc)))
    ORDER BY COMPLAINT_qry_containment_WIP.Works_Order_Number, COMPLAINT_qry_containment_WIP.Route_List_Code;
    OK, i have this in my query sql to pull top 1 operstat_index_No per works_Order_Number however it seems to just pull the top 1 record regardless (not by group)

    ANy help appreciated

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    using a GROUP BY and HAVING clause, in pace of the WHERE
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts