No equivalent to LEAST and GREATEST in SQL ?

[maelp]

trafficstars

What's up?

I have a table where each row has for example

temperature_a, temperature_b, etc

and I want to create a condition if min(temperature_a, temperature_b) < somethreshold OR max(temperature_a, temperature_b) > someOtherThreshold

in SQL I can do something like

    SELECT
        LEAST(value.temperature_front, value.temperature_back) as min_temp_cells,
        GREATEST(value.temperature_front, value.temperature_back) as max_temp_cells,
        GREATEST(value.temperature_mosfet, value.temperature_ic) as max_temp_bms
   FROM ...

but I don't see the equivalent in PRQL?

[maelp]

What would be a nicer way to do this than

from t = s"SELECT * FROM recent_cse(4)"
derive {
  min_temp_cells = s"LEAST(value.temperature_front, value.temperature_back)",
  max_temp_cells = s"GREATEST(value.temperature_front, value.temperature_back)",
  max_temp_bms = s"GREATEST(value.temperature_mosfet, value.temperature_ic)",
}
filter min_temp_cells < -10 || max_temp_cells > 40 || max_temp_bms > 70
group bms_id (
  aggregate {
    min_temp_cells = min min_temp_cells,
    max_temp_cells = max max_temp_cells,
    max_temp_bms = max max_temp_bms,
    count = count this,
  }
)

[max-sixty]

Yes we should probably add these functions. Using s-strings is the best approach for the moment.

---

For adding them — do we want to use LEAST & GREATEST? I always found these were slightly awkwardly named — they seem to use synonyms for MIN & MAX to to distinguish them from aggregation functions. But the words don't indicate whether they're aggregation functions or not.

We could instead use math.min & math.max given we have the advantage of modules. But possibly that's confusing...

[maelp]

I would probably keep least and greatest for consistency, or perhaps just have an "overload" of min which can be used when there are multiple params, eg min(someField) would be the aggregate of someField over the rows, and min(fieldA, fieldB, fieldC) would be the min of the field for each row (but might be confusing, don't know if there are contexts in which they could have both an "in-row" and "across row" meaning?)