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

cwiggler · #1 (**permalink**) 10-18-11, 10:26

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.

it-iss.com · #2 (**permalink**) 10-19-11, 03:51

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;

cwiggler · #3 (**permalink**) 10-20-11, 04:09

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

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;

r937 · #4 (**permalink**) 10-20-11, 07:28

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;

cwiggler · #5 (**permalink**) 10-21-11, 03:44

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

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;

r937 · #6 (**permalink**) 10-21-11, 08:05

what indexes have you declared on those tables? please do a SHOW CREATE TABLE for each one so that we can see them

Internet Services

Web Development

Digital Marketing

Consumer Tech