Analog of clickhouse any in vertica

Question

We can use next syntax in clickhouse:
select id, max(value1), any(value2) from scheme.table group by id
Any returns one of possible values and its behaviors isn't determinate. Is something like this in vertica?

I understand I can just use max or min functions but I need faster way

Answer 1

According to https://docs.vertica.com/23.4.x/en/sql-reference/functions/, no direct analog

but you could try use FIRST_VALUE + ORDER BY RANDOM()

SELECT DISTINCT 
 id, 
 FIRST_VALUE(value) OVER (PARTITION BY id ORDER BY RANDOM()) AS any_value 
FROM table

Answer 2

ANY() would return a randomly picked value, so I'll agree with @Slach's answer - but as your requirement is speed, I use Vertica's analytic limit clause in a dedicated query to randomly pick any value2 content, and join the main query with that.

WITH 
-- some sample data that we would have expected from you ....
indata(id,value1,value2) AS (                                                                                                                                                 
          SELECT 1,32,42
UNION ALL SELECT 1,33,43
UNION ALL SELECT 1,34,44
UNION ALL SELECT 1,35,45
UNION ALL SELECT 1,36,46
UNION ALL SELECT 2,32,42
UNION ALL SELECT 2,33,43
UNION ALL SELECT 2,34,44
UNION ALL SELECT 2,35,45
UNION ALL SELECT 2,36,46
)
-- real query starts here, replace following comma with "WITH"
,
anyvalue2 AS (
  SELECT
    id
  , value2
  FROM indata
  LIMIT 1 OVER(PARTITION BY id ORDER BY RANDOM())
)
SELECT
  indata.id
, MAX(value1) AS value1
, anyvalue2.value2
FROM indata
JOIN anyvalue2 USING(id)
GROUP BY 1,3
ORDER BY 1
;
-- out  id | value1 | value2
-- out ----+--------+--------
-- out   1 |     36 |     46
-- out   2 |     36 |     42