[mysql] duplicating lines

From: Weston, Craig (OFT) <Craig.Weston(at)oft.state.ny.us>
Date: Thu Sep 13 2007 - 12:48:54 EDT

Hello again,

            I am having a duplication of results problem. I believe my query (below) is giving me exactly what I deserve in writing it.  

What it returns to me is a row for each status. What I would most want would be a single row with the oldest status - IE the status datetime that happened earliest.  

What I am trying to do is determine when a service desk ticket first enters any one of these three categories. I am not using distinct on
`thedata2`.`Source` as this does not effect the result set.
 

I have 2 tables.

One of the tables lists all the ticket information at time of the ticket being closed. The other has an entry referenced by ticket number for each time a ticket is touched or updated. So what I am trying to do is identify the last time it was touched with the appropriate status change.  

Does anyone have any idea what I could do to eliminate the duplicate with the oldest time? I am experimenting in the idea of a subquery but can't think of anything else.    

???  

Thanks,

craig    

SELECT
`thedata2`.`Source`,

`thedata1`.`Status`,

`thedata2`.`Priority`,

`thedata1`.`start_Time`,

`thedata1`.`Close_Time`,

`thedata1`.`workday`'cycletime'

FROM
`thedata2`

Inner Join `thedata1` ON `thedata1`.`Source` = `thedata2`.`SourceSR`  

WHERE   (`thedata1`.`Status` like 'Resolved' OR

`thedata1`.`Status` like 'Restored' OR

`thedata1`.`Status` like 'Isolation')
 

and

`thedata2`.`Open_Time` BETWEEN '2007-02-01 00:00' AND '2007-08-31
23:59:59'

And  

((`thedata2`.`Priority` = 1 and `thedata1`.`workday` > 14400)

OR

(`thedata2`.`Priority` = 2 and `thedata1`.`workday` > 86400)

or

(`thedata2`.`Priority` = 2 and `thedata1`.`workday` > 172800))

---

This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. Received on Thu Sep 13 12:50:04 2007