Hi, I have narrowed the query - I just want to return the FIRST instance of each ticket, ie the Source. Here is the table:
CREATE TABLE `Thedata1` ( `Source` varchar(15) collate latin1_general_ci NOT NULL default '', `Close_Time` datetime NOT NULL default '0000-00-00 00:00:00', `start_Time` datetime default NULL, COMMENT 'correct time in Seconds' `start_Time2` datetime default NULL COMMENT 'Original Time in Seconds', `Original_Cycle_Time` decimal(11,4) default NULL, `workday` double(20,0) default NULL, PRIMARY KEY (`Source`,`start_Time2`,`Close_Time`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; My best guess on how to do this would be a self join? select `a1`.`Source, If(`a1`.`workday` < `a2`.`workday`, `a1`.`workday`,`a2`.`workday`) from `Thedata1` as `a1` inner join `Thedata1` as `a2` on `a1`.`Source` = `a2`.`table1` and `a1`.`workday` > `a2`.`workday` There can be more than 2 duplicates, and occasionally no duplicates. I really am not sure why this isn't working. Can anyone offer an insight? Thank you, Craig -----Original Message----- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Thursday, September 13, 2007 1:27 PM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: [mysql] duplicating lines Hi Craig, would you mind posting the SHOW CREATE TABLE for the tables in question? I'm having trouble determining what is the primary key for your service ticket table... Thanks, -Jay Weston, Craig (OFT) wrote: > 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. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
