Hi,
I have the following table:
CREATE TABLE DATA (
TARGET_TIME datetime NOT NULL,
SCENARIONAME varchar(20) NOT NULL,
TOOLNAME varchar(20) NOT NULL,
STATNAME varchar(100) NOT NULL,
STATVALUE int(10) NOT NULL,
PRIMARY KEY (TARGET_TIME, SCENARIONAME, SIMTOOLNAME, STATNAME)
);
I am trying to collect statistics from running tools and feeding them into
the DB.
If I have the following entries:
("2009-01-21 19:34:00", "scenario1", "tool1", "byte_count", 100),
("2009-01-21 19:34:00", "scenario1", "tool1", "udp_count", 200),
("2009-01-21 19:34:00", "scenario1", "tool1", "tcp_count", 300),
("2009-01-21 19:34:00", "scenario1", "tool2", "byte_count", 400),
("2009-01-21 19:34:00", "scenario1", "tool2", "udp_count", 500),
("2009-01-21 19:34:00", "scenario1", "tool2", "tcp_count", 600),
("2009-01-21 19:33:00", "scenario1", "tool1", "byte_count", 10),
("2009-01-21 19:33:00", "scenario1", "tool1", "udp_count", 20),
("2009-01-21 19:33:00", "scenario1", "tool1", "tcp_count", 30),
("2009-01-21 19:33:00", "scenario1", "tool2", "byte_count", 40),
("2009-01-21 19:33:00", "scenario1", "tool2", "udp_count", 50),
("2009-01-21 19:33:00", "scenario1", "tool2", "tcp_count", 60)
(Notice that the "old" targettime will be ignored in this particular query
as we are only interested in the "latest")
I would like to select only the most recent "targettime" within 1 minute and
only display only the rows that are "the latest" and print out all of the
stats as columns on a per toolname basis:
Targettime Scenario Toolname
byte_count udp_count tcp_count
2009-01-21 19:34:00 scenario1 tool1
100 200 300
2009-01-21 19:34:00 scenario1 tool2 400
500 600
The purpose of the query is to display the "latest statistics" for each
scenario/toolname group. It is important that the original data comes in
rows (statname may not be known ahead of time, so it must be stored as a
string in a field value).
I am using MySQL and have found some possibilities with SQL Server (Pivot,
crosstab, etc) ... but the requirements call for MySQL.
Is the above query possible?
Thanks a lot!
--
Attila
Software Developer
atteeela@stripped
