Mutliple Max's on Multiple reasons

[agravayne]

Hello,

 

I have a table with the following structure

 

CREATE TABLE  `tmxdata` (
  `ID` int(11) NOT NULL auto_increment,
  `ESN` bigint(20) NOT NULL,
  `Type` int(11) NOT NULL,
  `Value` varchar(255) default NULL,
  `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `EventTime` datetime NOT NULL,
  `Processed` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;

 

I want to try and get the last entry by eventtime for each ESN and Type.

 

Currently I am using a complicated query joining on the same table

 

SELECT speedtable.ESN, speedtable.Type, speedtable.Value as speed, max(speedtable.EventTime),
headtable.Type, headtable.Value as heading, max(headtable.EventTime),
celltable.Type, celltable.Value as cellid, max(celltable.EventTime),
sigtable.Type, sigtable.Value as signalstrength, max(sigtable.EventTime),
igtable.Type, igtable.Value as ignition, max(igtable.EventTime),
volt1.Type, volt1.Value as volt1contact, max(volt1.EventTime),
volt2.Type, volt2.Value as volt2contact, max(volt2.EventTime),
tempr.Type, tempr.Value as temperature, max(tempr.EventTime),
tempr2.Type, tempr2.Value as tempalarm, max(tempr2.EventTime),
batt.Type, batt.Value as battery, max(batt.EventTime),
batt2.Type, batt2.Value as battalarm, max(batt2.EventTime)                
FROM 
((((((((((tmxdata as speedtable right join tmxdata as headtable on speedtable.ESN=headtable.ESN) 
right join tmxdata as celltable on speedtable.ESN=celltable.ESN)
right join tmxdata as sigtable on speedtable.ESN=sigtable.ESN)
right join tmxdata as igtable on speedtable.ESN=igtable.ESN)
right join tmxdata as volt1 on speedtable.ESN=volt1.ESN)
right join tmxdata as volt2 on speedtable.ESN=volt2.ESN)
right join tmxdata as tempr on speedtable.ESN=tempr.ESN)
right join tmxdata as tempr2 on speedtable.ESN=tempr2.ESN)
right join tmxdata as batt on speedtable.ESN=batt.ESN)
right join tmxdata as batt2 on speedtable.ESN=batt2.ESN)
where speedtable.Type=2 and headtable.Type=4 and celltable.Type=17 and sigtable.Type=18 and igtable.Type=33 and volt1.type=34 and volt2.type=36 and tempr.type=49 and tempr2.type=50 and batt.type=52 and batt2.type=56
group by speedtable.ESN 

 

Howerer this is slow and is missing some entries..

Is there a better way to do this?

 

Here is a bit of sample data.

 

92,353358016023678,36,"01","2009-10-27 12:23:51","2009-10-27 12:23:51","N"

93,353358016023678,2,"0.51,5.86","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

94,353358016023678,4,"N,0.07624,1.77241","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

95,353358016023678,17,"33443442","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

96,353358016023678,18," 11","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

97,353358016023678,33,"01","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

98,353358016023678,34,"01","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

99,353358016023678,36,"01","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

100,353358016023678,49,"24","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

101,353358016023678,50,"100","2009-10-27 14:08:52","2009-10-27 14:08:50","N"

102,353358016023678,2,"0.51,5.86","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

103,353358016023678,4,"N,0.07624,1.77241","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

104,353358016023678,17,"33443442","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

105,353358016023678,18," 11","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

106,353358016023678,33,"01","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

107,353358016023678,34,"01","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

108,353358016023678,36,"01","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

109,353358016023678,49,"24","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

110,353358016023678,50,"100","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

111,353358016023678,52,"60000","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

112,353358016023678,56,"100","2009-10-27 14:11:44","2009-10-27 14:11:44","N"

113,899999,2,"0.51,5.86","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

114,899999,4,"N,0.07624,1.77241","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

115,899999,17,"33443442","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

116,899999,18," 11","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

117,899999,33,"01","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

118,899999,34,"01","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

119,899999,36,"01","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

120,899999,49,"24","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

121,899999,50,"100","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

122,899999,52,"60000","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

123,899999,56,"100","2009-10-27 14:24:46","2009-10-27 14:24:46","N"

124,899999,2,"0.51,5.86","2009-10-27 14:41:16","2009-10-27 14:41:16","N"

125,899999,17,"33443442","2009-10-27 14:41:16","2009-10-27 14:41:16","N"

126,899999,18," 11","2009-10-27 14:41:16","2009-10-27 14:41:16","N"

127,899999,33,"01","2009-10-27 14:41:16","2009-10-27 14:41:16","N"

 

Thanks

Scott

[fenway]

Yikes... what are you trying to do there?