List:General Discussion« Previous MessageNext Message »
From:Gian Karlo C Date:October 18 2011 10:20am
Subject:How to get the first data from a multiple or duplicate records
View as plain text  
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.

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@stripped | fixed
 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 |
user@stripped | fixed
 123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 18:20:33 | 00:02:07 |
user@stripped | fixed
 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:21:33 | 00:02:07 |
user@stripped | fixed
 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 |
user@stripped | fixed
 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:25:33 | 00:02:07 |
user@stripped | fixed
 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 18:20:33 | 00:02:07 |
user@stripped | fixed
 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:29:33 | 00:02:07 |
user@stripped | 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?

123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 |
user@stripped | fixed
234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 |
user@stripped | fixed
 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 |
user@stripped | fixed

Here's my statement.

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@stripped'
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.

Thread
How to get the first data from a multiple or duplicate recordsGian Karlo C18 Oct