Get maximum value thorough a calculated table

Question

question

Julio R asked Jun 6, '23 Julio R commented Jun 8, '23

Get maximum value thorough a calculated table

Hello,

I want to use the standard Output by hour by type dashboard to get also a table with the maximum value by Type.

To do this, I installed the Statistic and created a calculated table listening to the "Output By Hour By Type Collector" statistic collector that was created when I installed the Statistic.

What I need is the same table but that only display one row with the highest throughput value for each Type.

Then, I tried to use the Query builder interface, but I was not able to do it so I tried to modify the Query manually, and I dont undestand what Im doing wrong.

Is there an wasier way to achieve this or what am I doing wrong in my query?

Here is the SQL:

SELECT

Type AS [Partition 1]

, Time AS [Metric 1]

, Type AS [Metric 2]

, Throughput AS [Metric 3]

FROM [Output By Hour By Type Collector]

WHERE [Metric 3] = (SELECT MAX([Metric 3]) FROM [Output By Hour By Type Collector])

ORDER BY [Partition 1] ASC

And here is the example model:

Ejemplo tabla con máximos (Calculated table).fsm

Software Version:

FlexSim 23.0.5

sql calculated table

ejemplo-tabla-con-maximos-calculated-table.fsm (45.2 KiB)

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2023-06-06T20:00:34Z

Felix Möhlmann answered Jun 6, '23 Julio R commented Jun 8, '23

I can't say why your syntax is not working. I did find a working alternative using JOIN though.

SELECT a.Time, a.Type, b.Max FROM [Output By Hour By Type Collector] AS a
INNER JOIN (
    SELECT Type, MAX(Throughput) AS Max
    FROM [Output By Hour By Type Collector]
    GROUP BY Type) AS b
ON a.Throughput == b.Max
ORDER BY Type ASC

Taken from here (top answer):
https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column

If the max value appears more than once this will return all rows with that value.

ejemplo-tabla-con-maximos-calculated-table_1.fsm

ejemplo-tabla-con-maximos-calculated-table-1.fsm (46.0 KiB)

· 3

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Julio R commented · Jun 06, 2023 at 08:11 PM

Thank you @Felix Möhlmann ,

This works most of the times, but sometimes it adds more rows that what I need, for example at second 1413.29 this is the table it shows (to the right is the current table):

Why does sometimes shows these additional tables?

0 ·

1686082235216.png (19.4 KiB)

Felix Möhlmann Julio R commented · Jun 07, 2023 at 04:17 AM

Sorry for the oversight, you have to join on both the throughput value and the type. Otherwise, if the maximum value is the same for multiple types, those rows qualify more than once.

ON a.Throughput == b.Max AND a.Type == b.Type

0 ·

Julio R Felix Möhlmann commented · Jun 08, 2023 at 12:29 AM

Thants exactly what I want it.

Thank you!

0 ·

question