MySQL Forums
Forum List  »  Newbie

Duplicate lines but I cant seem to remove them,
Posted by: Jamie Gagnon
Date: February 18, 2014 02:58AM

I have a script (an ugly one but bear in mind I am new to SQL and I am limited in what I can do so you will no doubt hate the case statements in it) that is basically pulling back information for a bug history table but only the last transition status of the bug. The problem is that the way I have written it it still brings back duplicate bug id's when the date_modified is the same for the transition status.

select distinct bughist.id,bughist.bug_id,bughist.field_name,
case

when bughist.old_value = 10 then 'New'

when bughist.old_value = 20 then 'Feedback'

when bughist.old_value = 40 then 'Confirmed'

when bughist.old_value = 50 then 'Assigned'

when bughist.old_value = 55 then 'Deferred'

when bughist.old_value = 60 then 'RFT'

when bughist.old_value = 80 then 'Resolved'

when bughist.old_value = 90 then 'Released'

when bughist.old_value = 95 then 'Rejects'

Else NULL
End as 'Old_value',

case
when bughist.new_value = 10 then 'New'

when bughist.new_value = 20 then 'Feedback'

when bughist.new_value = 40 then 'Confirmed'

when bughist.new_value = 50 then 'Assigned'

when bughist.new_value = 55 then 'Deferred'

when bughist.new_value = 60 then 'RFT'

when bughist.new_value = 80 then 'Resolved'

when bughist.new_value = 90 then 'Released'

when bughist.new_value = 95 then 'Rejects'

Else NULL
End as 'New_value',bughist.type, FROM_UNIXTIME(bughist.date_modified) as 'Date Modified',FROM_UNIXTIME(bug.date_submitted) as 'Date Submitted',

case

WHEN bug.severity = 10 then 'Feature Request'

WHEN bug.severity = 20 then 'Trivial'
WHEN bug.severity = 50 then 'Minor'
WHEN bug.severity = 60 then 'Major'
WHEN bug.severity = 70 then 'Crash'

else NULL

END as 'Severity',

case

WHEN bug.status = 10 then 'New'
WHEN bug.status = 20 then 'Feedback'

WHEN bug.status = 40 then 'Confirmed'
WHEN bug.status = 50 then 'Assigned'

WHEN bug.status = 55 then 'Deferred'

WHEN bug.status = 60 then 'RFT'

WHEN bug.status = 80 then 'Resolved'
WHEN bug.status = 90 then 'Released'

WHEN bug.status = 95 then 'Rejects'

else NULL

END as 'Status',

case

WHEN bug.resolution = 10 then 'New'

WHEN bug.resolution = 20 then 'Fixed'
WHEN bug.resolution = 30 then 'Reopened'
WHEN bug.resolution = 40 then 'Not able to reproduce'

WHEN bug.resolution = 50 then 'Not Fixable'

WHEN bug.resolution = 60 then 'Duplicate'

WHEN bug.resolution = 70 then 'No CVhange Required'

WHEN bug.resolution = 100 then 'Not a Defect'

WHEN bug.resolution = 110 then 'As Designed'

WHEN bug.resolution = 120 then 'Feature Request'

else NULL

END as 'Bug Table Resolution'

from bugsystem_bug_history_table bughist

left join bugtracker.bugsystem_bug_table bug

on bughist.bug_id = bug.id

left outer join bugsystem_bug_history_table bughist2
on (bughist.bug_id = bughist2.bug_id and bughist.date_modified < bughist2.date_modified)
where bughist.field_name = 'status' and bug.project_id in (1,3,4,13) and bughist2.bug_id is null and bughist.date_modified >= '1356998400'
group by bughist.id
order by bughist.bug_id

This returns rows like this but in the example below you can see I have a row with same bug id because the date modified is the same as the previous one. (The second row is the correct status

id|bugid|field_name|old_value| New_Value|type|date_Modified |Date_submitted.......
1 |1 |Status |Confirmed|Released |0 |2013-12-04 16:04:10|......
2 |1 |Status |Released |Rejected |0 |2013-12-04 16:04:10|......
3 |2 |Status |New |Confirmed |0 |2013-02-02 09:00:00|...


I have tried using distinct but it does not seem to rid me of those duplicates. Each line has a unique ID so was thinking perhaps I need to do something in my query to get rid of the duplicates using the ID but I can't figure it out.

Any advice would be appreciated.

Options: ReplyQuote


Subject
Written By
Posted
Duplicate lines but I cant seem to remove them,
February 18, 2014 02:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.