Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    1

    Unanswered: Need a way to exclude duplicate information

    I've created a query:
    SELECT DISTINCT co.CL_Desc,a.AN_Key,a.CL_Code,a.AT_Code,a.AN_Refer ence,a.AN_ToDo,a.An_Closed
    ,at2.CU_Value2,an1.CU_Value1,at3.CU_Value5 /* INNER JOIN FIELDS */
    FROM clients co
    LEFT OUTER JOIN Action a ON co.CL_Code = a.CL_Code /* Get actions */
    LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
    LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
    LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
    WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
    AND at2.CU_Value2='40201'
    AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
    ORDER BY CL_Desc,a.AN_Reference

    And it returns somthing like

    CompanyA,Enquiry,04/09/2003
    CompanyA,Contract,05/09/2003
    ComanyB,Enquiry,04/09/2003

    And so on
    I know this is not true duplcates but for me its a problem. I only want the last record of each company to display being the most recent record. I thought I union query may have helped to solve this, but no luck has anyone got any ideas?

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Need a way to exclude duplicate information

    What is your last record? The record with the highest ID per company? Select those ID's per company, and INNER JOIN the result with your original table!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DB's general solution in code:
    (I assumed that AN_Closed was the date value and that you wanted the latest one, rather than an ID value.) Because you are using OUTER joins, I think you need two levels of nesting to be sure you get all the data you want, but I couldn't test the code.

    SELECT DISTINCT co.CL_Desc, a.AN_Key, a.CL_Code, a.AT_Code, a.AN_Reference, a.AN_ToDo, a.An_Closed, at2.CU_Value2, an1.CU_Value1, at3.CU_Value5 /* INNER JOIN FIELDS */
    FROM clients co
    LEFT OUTER JOIN /* Get last actions */
    LEFT OUTER JOIN
    (select An_Key, CL_Code, AT_Code, AN_Reference, AN_ToDo, AN_Closed
    from Action
    inner join (select CL_Code, max(AN_Closed) Last_AN_Closed from Action group by CL_Code) Last_Values
    on Action.CL_Code = Last_Values.CL_Code and Action.AN_Closed = Last_Values.Last_An_Closed) a
    ON co.CL_Code = a.CL_Code
    LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
    LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
    LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
    WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
    AND at2.CU_Value2='40201'
    AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
    ORDER BY CL_Desc,a.AN_Reference

    blindman

Posting Permissions

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