Group wise maximum

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)

      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,
Do you need help?
In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software:
Learn more about Pantek's Technical Support offerings
Place an order for 24/7/365 Technical Support here
Learn more about Pantek, who we are, and what we do
Chat with a Live Operator
Related lists.mysql.com Links
announce
benchmarks
bugs
certification
cluster
commits
dotnet
gui-tools
internals
java
maxdb
myodbc
mysql
mysqldoc
packagers
perl
plusplus
replication
win32
Request more information about Pantek
                          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 ;

+------------------------+------------------+------+-----+---------+----------------+

| 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    |      

         |
+------------------------+------------------+------+-----+---------+----------------+

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/mysql?unsub=lists@pantek.com

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek