Thank you Jay and Baron for your help.
-------------------------------------------------------- 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. -----Original Message----- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, September 14, 2007 12:28 PM To: Weston, Craig (OFT) Cc: Jay Pipes; mysql@lists.mysql.com Subject: Re: [mysql] duplicating lines I think you want to use a GROUP BY query. Try this article: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row -per-group-in-sql/ Baron Weston, Craig (OFT) wrote: > 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]
