An example, which use a subquery (available from MySQL 4.1):
select eqid, paramid, lastmodified, value from eq_deltalist param1
where lastmodified = (SELECT max(lastmodified) FROM eq_deltalist
WHERE paramid = param1.paramid AND eqid = param1.eqid AND lastmodified <= now())
You would probably want to join the outer of the two queries with eq and eq_params also
Regards /Karlsson Niklas Karlsson wrote:
I'd like to setup an application to store equipment configuration data in a MySql database. The basic setup is that each equipment has a certain number of parameters which may change over time. I'd like to track these changes over time and want to be able to create queries which determine the status a certain date.
I guess the easiest approach is to have one column per parameter and simply store the value of all parameters whenever I read up the configuration data using the Equipment Id and Date as keys. However, I'd like to store only the "delta" information, I.e. data changed between different dates.
To give an idea, I need to be able to track around 100 parameters for roughly 10000 different equipments so performance is an issue.
Assuming now I create the following tables;
CREATE TABLE `eq` (
`eqid` int(11) NOT NULL auto_increment,
`eqname` char(10) default NULL,
PRIMARY KEY (`eqid`)
)
CREATE TABLE `eq_params` (
`paramid` int(4) NOT NULL default '0',
`paramname` char(10) default NULL,
PRIMARY KEY (`paramid`)
)
CREATE TABLE `eq_deltalist` (
`eqid` int(4) NOT NULL default '0',
`paramid` int(11) NOT NULL default '0',
`lastmodified` datetime NOT NULL default '0000-00-00 00:00:00',
`value` double(15,3) default NULL,
PRIMARY KEY (`eqid`,`paramid`,`lastmodified`)
)
If I define my equipments in "eq", the different parameters in "eq_params" and each change of given parameter in "eq_deltalist", how do I query for the valid parameters a certain date (i.e. when the "lastmodified" date is closest to the date in question) ??
Thankful for any good ideas.
BR // Niklas
-- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm <___/ www.mysql.com Cellphone: +46 708 608121
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
