Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Unanswered: How to get the first data from a multiple or duplicate records

    Hello everyone, I would like to ask for idea and help on how to achieve my concern. Below is my SQL statement. Im joining 2 tables to get my results. Here's the sample results of what im getting.

    Code:
    Name  | Desc | Issue | ATime | Back | TotalTime | Ack | Res
     
    123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | user@home.net | fixed
    234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | user@home.net | fixed
    123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 18:20:33 | 00:02:07 | user@home.net | fixed
    234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:21:33 | 00:02:07 | user@home.net | fixed
    223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | user@home.net | fixed
    234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:25:33 | 00:02:07 | user@home.net | fixed
    223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 18:20:33 | 00:02:07 | user@home.net | fixed
    234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:29:33 | 00:02:07 | user@home.net | fixed
    What I want to achieve is to get only the first entry of data based on Back and do my calculation. how can I do that?

    Code:
    123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | user@home.net | fixed
    234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | user@home.net | fixed
    223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | user@home.net | fixed
    Here's my statement.

    Code:
    SELECT t1.name, 
    t1.Description, 
    t1.Issue, 
    t1.Dateres AS ATime, 
    t2.Dateres AS BAck, 
    TIMEDIFF(t2.Dateres,t1.Dateres) AS TotalTime, 
    t2.Acknowledge, t2.Resolution 
    FROM t1 LEFT JOIN t2 ON t1.name = t2.name 
    AND t1.IPAddress = t2.IPAddress 
    AND t1.Description = t2.Description 
    AND t1.Issue = t2.Issue 
    AND t1.Severity = t2.Severity 
    AND t1.Timestamp = t2.Timestamp 
    WHERE t1.Dateres is NOT NULL AND t2.Dateres is NOT NULL 
    AND t2.Acknowledge = 'user@home.net' 
    AND t2.Dateres >= '2011-10-18 00:00:00' 
    AND t2.Dateres <= '2011-10-23 23:59:59' 
    GROUP BY ATime ORDER by BAck ASC;
    Thanks.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try using the DISTINCT:

    Code:
    SELECT DISTINCT t1.name, 
    t1.Description, 
    t1.Issue, 
    t1.Dateres AS ATime, 
    t2.Dateres AS BAck, 
    TIMEDIFF(t2.Dateres,t1.Dateres) AS TotalTime, 
    t2.Acknowledge, t2.Resolution 
    FROM t1 LEFT JOIN t2 ON t1.name = t2.name 
    AND t1.IPAddress = t2.IPAddress 
    AND t1.Description = t2.Description 
    AND t1.Issue = t2.Issue 
    AND t1.Severity = t2.Severity 
    AND t1.Timestamp = t2.Timestamp 
    WHERE t1.Dateres is NOT NULL AND t2.Dateres is NOT NULL 
    AND t2.Acknowledge = 'user@home.net' 
    AND t2.Dateres >= '2011-10-18 00:00:00' 
    AND t2.Dateres <= '2011-10-23 23:59:59' 
    GROUP BY ATime ORDER by BAck ASC;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2011
    Posts
    6
    Hi I tried DISTINCT but still showing duplicate records. Any solution how to implement MIN on TotalTime ? thanks

    Quote Originally Posted by it-iss.com View Post
    Try using the DISTINCT:

    Code:
    SELECT DISTINCT t1.name, 
    t1.Description, 
    t1.Issue, 
    t1.Dateres AS ATime, 
    t2.Dateres AS BAck, 
    TIMEDIFF(t2.Dateres,t1.Dateres) AS TotalTime, 
    t2.Acknowledge, t2.Resolution 
    FROM t1 LEFT JOIN t2 ON t1.name = t2.name 
    AND t1.IPAddress = t2.IPAddress 
    AND t1.Description = t2.Description 
    AND t1.Issue = t2.Issue 
    AND t1.Severity = t2.Severity 
    AND t1.Timestamp = t2.Timestamp 
    WHERE t1.Dateres is NOT NULL AND t2.Dateres is NOT NULL 
    AND t2.Acknowledge = 'user@home.net' 
    AND t2.Dateres >= '2011-10-18 00:00:00' 
    AND t2.Dateres <= '2011-10-23 23:59:59' 
    GROUP BY ATime ORDER by BAck ASC;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t1.name
         , t1.Description
         , t1.Issue
         , t1.Dateres AS ATime
         , t2.Dateres AS BAck
         , TIMEDIFF(t2.Dateres,t1.Dateres) AS TotalTime
         , t2.Acknowledge
         , t2.Resolution
      FROM t1 
    LEFT 
      JOIN ( SELECT name                      
                  , MIN(t2.Dateres) AS earliest
               FROM t2
              WHERE t2.Acknowledge = 'user@home.net'
                AND t2.Dateres >= '2011-10-18'
                AND t2.Dateres  < '2011-10-24'
             GROUP
                 BY name ) AS m2     
        ON m2.name        = t1.name
    LEFT 
      JOIN t2 
        ON t2.name = m2.name
       AND t2.Dateres = m2.earliest   
     WHERE t1.Dateres is NOT NULL 
    ORDER 
        BY BAck ASC;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2011
    Posts
    6
    Hi and thanks. I tried your suggestion but it take forever to run the query. i don't know why. I left the query running for 5 mins but still doesn't show results. any idea?

    Quote Originally Posted by r937 View Post
    Code:
    SELECT t1.name
         , t1.Description
         , t1.Issue
         , t1.Dateres AS ATime
         , t2.Dateres AS BAck
         , TIMEDIFF(t2.Dateres,t1.Dateres) AS TotalTime
         , t2.Acknowledge
         , t2.Resolution
      FROM t1 
    LEFT 
      JOIN ( SELECT name                      
                  , MIN(t2.Dateres) AS earliest
               FROM t2
              WHERE t2.Acknowledge = 'user@home.net'
                AND t2.Dateres >= '2011-10-18'
                AND t2.Dateres  < '2011-10-24'
             GROUP
                 BY name ) AS m2     
        ON m2.name        = t1.name
    LEFT 
      JOIN t2 
        ON t2.name = m2.name
       AND t2.Dateres = m2.earliest   
     WHERE t1.Dateres is NOT NULL 
    ORDER 
        BY BAck ASC;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what indexes have you declared on those tables? please do a SHOW CREATE TABLE for each one so that we can see them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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