The two example queries show the average of nested maximums. In the first query, the table is a PTI table and the second example shows a non-PTI table:
/*PTI Table*/ SELECT AVG(TEMP_MAX) FROM ( SELECT MAX(TEMPERATURE) AS TEMP_MAX FROM OCEAN_BUOYS WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' GROUP BY TIME (MINUTES(10) AND BUOYID) ) AS NESTED_MAX_TABLE; /*Non-PTI Table*/ SELECT AVG(TEMP_MAX) FROM ( SELECT MAX(TEMPERATURE) AS TEMP_MAX FROM OCEAN_BUOYS_NONPTI WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' GROUP BY TIME (MINUTES(10) AND BUOYID) USING TIMECODE(TIMECODE) ) AS NESTED_MAX_TABLE;
The results get loaded into the derived table NESTED_MAX_TABLE. They are shown in ascending order, but this is not necessarily the expected order of the values in the derived table.
The results of both queries are identical, so only one result set is shown.
TEMP_MAX |
---|
43 |
56 |
79 |
99 |
100 |
Result: The actual result of the maximum of results (which are nested in the NESTED_MAX_TABLE):
Average(TEMP_MAX) ----------------- 75