Median of three columns (for each row) in Vertica SQL

Question

My table looks like this:

Product	Length	Width	Height
A	34	22	10
B	40	2	12

I would like to get the longest, second longest and third longest side for each product. For the longest and third longest it was easy, I did it like with GREATEST() and LEAST(). For the second longest for the moment I am doing it like this with a subquery, but this makes the code longer and not really clean.

SELECT
product,
longest_side,
third_longest_side,
width + height + length - longest_side - third_longest_side AS second_longest_side
FROM (
      SELECT
      product,
      GREATEST(width, height, length)      AS longest_side,
      LEAST(width, height, length)         AS third_longest_side
      ...
     )

The MEDIAN() function would solve my problems, but this does not accept more than one column as values. The example below unfortunately does not work. Do you know any similar function that would allow me to do that without a subquery?

SELECT
product,
GREATEST(width, height, length)      AS longest_side,
LEAST(width, height, length)         AS third_longest_side,
MEDIAN(width, height, length)        AS second_longest_side
...

Answer 1

Verticalise, use OLAP functions, then GROUP BY again:

-- your input
WITH
indata(prd,l,w,h) AS (
          SELECT 'A',34,22,10
UNION ALL SELECT 'B',40, 2,12
)
,
-- need three key rows ....
t(t) AS (
          SELECT 'l'
UNION ALL SELECT 'w'
UNION ALL SELECT 'h'
)
,
vertical AS (
SELECT
  prd
, t
, CASE t 
    WHEN 'l' THEN l
    WHEN 'w' THEN w
    WHEN 'h' THEN h 
  END AS v
FROM indata CROSS JOIN t
)
-- test query ...
-- SELECT * FROM vertical 
-- out  prd | t | v  
-- out -----+---+----
-- out  A   | l | 34
-- out  A   | w | 22
-- out  A   | h | 10
-- out  B   | l | 40
-- out  B   | w |  2
-- out  B   | h | 12
,
olap AS (
  SELECT 
    *
  , MAX(v) OVER w AS longest_side
  , MIN(v) OVER w AS shortest_side
  , MEDIAN(v) OVER w AS medium_side
  FROM vertical
  WINDOW w AS (PARTITION BY prd)
)
-- test query ...
-- SELECT * FROM olap;
-- out  prd | t | v  | longest_side | shortest_side | medium_side 
-- out -----+---+----+--------------+---------------+-------------
-- out  B   | w |  2 |           40 |             2 |          12
-- out  B   | h | 12 |           40 |             2 |          12
-- out  B   | l | 40 |           40 |             2 |          12
-- out  A   | h | 10 |           34 |            10 |          22
-- out  A   | w | 22 |           34 |            10 |          22
-- out  A   | l | 34 |           34 |            10 |          22
SELECT
  prd
, MAX(longest_side)  AS longest_side
, MAX(shortest_side) AS shortest_side
, MAX(medium_side)   AS medium_side
FROM olap
GROUP BY 1;
-- out  prd | longest_side | shortest_side | medium_side 
-- out -----+--------------+---------------+-------------
-- out  A   |           34 |            10 |          22
-- out  B   |           40 |             2 |          12