CODEWITHSUNDEEP
mysqljoin
web.curious.gk
web.curious.gk

Reputation: 167

Implement MIN or get the lowest record

I would like to ask for help on how to fix this. I would like to add a MIN or some sort of query to only show lowest number on the TotalTime base on the SQL query below.

The query is working but it show duplicate data and I only want to get the lowest TotalTime.

Thanks in advance.

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 = '[email protected]' 
  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;

Upvotes: 0

Views: 67

Answers (2)

Sashi Kant
Sashi Kant

Reputation: 13465

SELECT 
t1.name, 
t1.Description, 
t1.Issue, 
t1.Dateres AS ATime, 
t2.Dateres AS BAck, 
Min(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 = '[email protected]' 
AND t2.Dateres >= '2011-10-18 00:00:00' 
AND t2.Dateres <= '2011-10-23 23:59:59' 
GROUP BY t1.name ORDER by BAck ASC;

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

Hope this will work:::

SELECT DISTINCT t1.name, 
t1.Description, 
t1.Issue, 
t1.Dateres AS ATime, 
t2.Dateres AS BAck, 
Min(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 = '[email protected]' 
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;

Upvotes: 1

Related Questions