Min/max a value with a fixed number using SQL

CrateDB SQL
1

Hi,

I’m trying to figure out how to get the minimul of 2 values (or max, or clamp between two values) when doing an SQL query in CrateDB.

I’d like to be able to do a query that is something like this:

SELECT
  time,
  MIN(AVG(value), 40) AS "Very Low",
  MIN(AVG(value), 120) AS "Low",
  MIN(AVG(value), 200) AS "Moderate",
  MIN(AVG(value), 280) AS "High",
  AVG(value) AS "Very High",
FROM
  data_table
GROUP BY
  time
ORDER BY
  time ASC

The above doesn’t work, but I was hoping someone would be able to help me get the same thing another way.

Thanks

1 Like
2

Hi @HughMacd,

Welcome to our community! Are the functions GREATEST and LEAST what you are looking for?

Example:

SELECT time,
       LEAST(AVG(value), 40) AS "Very Low",
       ...
FROM data_table
GROUP BY time
ORDER BY time ASC;
1 Like
3

Ah - that looks like it’ll do exactly what I’m after. I’m not able to test for sure right now but I’ll give it a go later.

Thanks

1 Like
4

This did exactly what I was after - thanks @hammerhead

1 Like