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 attee...@gmail.com