Find the max of one column, sorted by another column, and get the whole row

[jonc]

Hello! I have data that includes various metrics collected for several servers denoted by hostname, and multiple rows per server since the metrics are collected each day. These are collected via RestAPI, so they come back in JSON objects.

I would like to find the max value of one of the metrics (keyMetric) across the whole data set, for each server individually (grouped by hostname), and then get the rest of the metrics (otherStuff) for that day ("row") that the max occurred on.

I've been trying to do this with Query JSON with SQL and have gotten close, but not quite there.

Here is an example data set after getting everything with Query JSON with SQL:

hostname       |  keyMetric     |   date      |   otherStuff
hostname1      |       90       | 2022-12-19  |       55
hostname1      |       20       | 2022-12-20  |       66
hostname2      |       30       | 2022-12-19  |       77
hostname2      |       80       | 2022-12-20  |       44

Desired output of the SQL:

hostname       |  keyMetric     |   date      |   otherStuff
hostname1      |       90       | 2022-12-19  |       55
hostname2      |       80       | 2022-12-20  |       44

The closest I've come (I think) is with something like this:

SELECT
  hostname,
  keyMetric,
  createdDate::TIMESTAMP::DATE AS date,
  otherStuff
FROM {{getASMetricsAllHosts.data}} WHERE hostname IN
( SELECT hostname, MAX(keyMetric), date
  FROM {{getASMetricsAllHosts.data}}
  GROUP BY hostname
)

But I haven't been able to return only the one row corresponding to the max value - it's still returning all rows.
(Note that the createdDate field comes back like "2022-12-19T20:00:23.119500-07:00" so I've simplified it with the ::TIMESTAMP::DATE, but there's still only one per day in the data set)

Any ideas on how to get just the one row for each server that corresponds to the max value of keyMetric?

Many thanks!

[dcartlidge]

I cheated and used the included lodash functions instead of SQL, this is not great code by any stretch but it was fun to write

let d = [
  {hostname: 'hostname1', keyMetric: 90, date: '2022-12-19', otherStuff: 55},
  {hostname: 'hostname1', keyMetric: 20, date: '2022-12-20', otherStuff: 66},
  {hostname: 'hostname2', keyMetric: 30, date: '2022-12-19', otherStuff: 77},
  {hostname: 'hostname2', keyMetric: 80, date: '2022-12-20', otherStuff: 44}
]

let maxOfEverything = _.last(_.sortBy(d, 'keyMetric'));

let maxByHostname = _.keyBy(_.map(_.groupBy(d, 'hostname'), function(x) { return _.last(_.sortBy(x, 'keyMetric'));}), 'hostname');

return {maxOfEverything, maxByHostname}

{
    "maxOfEverything": {
        "hostname": "hostname1",
        "keyMetric": 90,
        "date": "2022-12-19",
        "otherStuff": 55
    },
    "maxByHostname": {
        "hostname1": {
            "hostname": "hostname1",
            "keyMetric": 90,
            "date": "2022-12-19",
            "otherStuff": 55
        },
        "hostname2": {
            "hostname": "hostname2",
            "keyMetric": 80,
            "date": "2022-12-20",
            "otherStuff": 44
        }
    }
}

[ScottR]

@jonc Have you tried using MAX function in postgres?

PostgreSQL MAX Function: Get Maximum Value in a Set

[jonc]

@dcartlidge Thank you for the response! This gives me the right values after I used your method as a Transformer in my RestAPI Resource Query, right on!

However, the JSON formatting seems to be hard for Retool to handle - if I try to put it into a table, Retool complains like:

Invalid value. Try passing in an array of objects, or the result of a SQL Query with something like `{{ query1.data }}`

I'm also unable to use Query JSON with SQL on the output to further refine it.

I only need "maxByHostname" - Is there a way to have the values returned be "flatter" in JSON, e.g.

[
  {
     "hostname": "hostname1",
     "keyMetric": 90,
     "date": "2022-12-19",
     "otherStuff": 55
  },
  {
     "hostname": "hostname2",
     "keyMetric": 80,
     "date": "2022-12-20",
     "otherStuff": 44
  }
]

Or is there another way to handle the JSON in Retool tables and graphs?

Thanks again!

[jonc]

@ScottR Thank you for the response! Yeah, I've tried the MAX function - see my attempt in the original post. It's easy to get the max value, but the issue has been getting all the rest of the data on the same row as the max, and grouping by hostname as well.

Any ideas?

[ScottR]

Yes, sorry about that.... I took

[
  {
     "hostname": "hostname1",
     "keyMetric": 90,
     "date": "2022-12-19",
     "otherStuff": 55
  },
  {
     "hostname": "hostname2",
     "keyMetric": 80,
     "date": "2022-12-20",
     "otherStuff": 44
  }
]

and added it to a table and it works....
Maybe I am missing your next ask....

[jonc]

@ScottR No worries!

Yep, that is the formatting I'm hoping for. But the output of @dcartlidge's function has different JSON formatting - see above, and it looks like this:

{
    "maxOfEverything": {
        "hostname": "hostname1",
        "keyMetric": 90,
        "date": "2022-12-19",
        "otherStuff": 55
    },
    "maxByHostname": {
        "hostname1": {
            "hostname": "hostname1",
etc.

I haven't figured out how to get Retool to work with that type of JSON formatting.

[jonc]

I think it needs to be an array of objects, but I haven't been able to figure out how to make it so in the transformer.

[Tess]

Hey there! What about {{Object.values(queryname.data.maxByHostname)}} to get it formatted properly for the table?

[jonc]

@Tess Bingo! That is exactly what I need to get @dcartlidge's transformer to work. Thank you so much!