I just posted a question with a subject of "Revised optimization question"
and did some more searching and found the my problem should be titled
"group-wise maximum".
I need the group-wise maximum of this query based on payload_time:
CREATE PROCEDURE `getElement`(IN id INT UNSIGNED,
IN ptime DOUBLE,
IN tid VARCHAR(255),
IN exact_time TINYINT)
BEGIN
IF(ptime < 1) THEN
SELECT MAX(ROUND(payload_time,6)) FROM data__ProcessedDataFrames
WHERE test_id=tid INTO ptime;
END IF;
SELECT
J.product_id,
P.processed_id,
MAX(ROUND(P.payload_time,6)) as payload_time,
P.top_level_product_name,
SUBSTR(
BINARY(processed_data),
FLOOR(J.product_offset/8)+1,
CEIL(J.product_length/8)) as substring,
(SELECT HEX(substring)) as raw_data,
(SELECT toString(
substring,
round(char_length(raw_data)/2,0),
data_type,
(SELECT attribute_value FROM
config__DataProductAttributes
WHERE attribute_name='FormatString'
AND config__DataProductAttributes.product_id=
J.product_id),
product_offset % 8,
(product_length+(product_offset % 8)) % 8,
product_length,
byte_order,
(SELECT attribute_value FROM
config__DataProductAttributes
WHERE attribute_name = 'ConvParams'
AND config__DataProductAttributes.product_id =
J.product_id))) as converted_data,
(SELECT enum_name FROM
config__DataProductEnumConversions
WHERE product_id=J.product_id
AND enum_value =converted_data) as enumerated_data,
(SELECT metricTest(converted_data,
(SELECT xmlTestMetric FROM
test__TestMetrics
WHERE product_id = J.product_id))) as test_metric
FROM data__ProcessedDataFrames
P INNER JOIN
(SELECT E.product_id,
top_level_product_name,
product_length,
product_offset,
data_type,
byte_order
FROM display__DataProducts_in_Element
E INNER JOIN config__DataProducts D ON
E.product_id=D.product_id
WHERE E.element_id=id) J
ON P.top_level_product_name=J.top_level_product_name
WHERE P.test_id=tid
AND payload_time <= ptime
GROUP BY J.product_id ;
END;;
mysql> desc data__ProcessedDataFrames;
+------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+------------------------+------------------+------+-----+---------+----------------+
| processed_id | int(10) unsigned | NO | PRI | NULL |
auto_increment |
| top_level_product_name | varchar(255) | YES | MUL | NULL |
|
| test_id | int(10) unsigned | YES | MUL | NULL |
|
| payload_time | double | YES | MUL | NULL |
|
| universal_time | double | YES | | NULL |
|
| processed_data | mediumblob | YES | | NULL |
|
+------------------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql>
This table can get quite large so I'm trying not to query on it twice to
get the MAX(payload_time) < ptime. Obviously what I have is not the
group-wise maximum I was hoping for.
Dave G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
