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 ...
Advertisement
Answer
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