CODEWITHSUNDEEP
sqlamazon-redshift
Eduardo EPF
Eduardo EPF

Reputation: 170

How to order columns by size

I have a table with length, width and height columns and I would like to order them so that I have a new table named greatest_dim, mid_dim, min_dim, or going for general, 1st_dim, 2nd_dim etc

I know in redshift we have the greatest and least functions but this will not let me to get the middle position. For this case that I want to order 3 dimensions I could get the greatest of three and the least of three in a subquery and then have a case with and conditions to get the middle value but I am wondering if there is a simpler/more efficient way to do it

Upvotes: 1

Views: 204

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

For only three values it's not so difficult. You have GREATEST, LEAST, and the sum minus the two for the other value:

select
  greatest(length, width, height) as greatest_dim,
  length + width + height - greatest(length, width, height)
                          - least(length, width, height) as mid_dim,
  least(length, width, height) as min_dim
from mytable;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This is a pain. One method is complex case expressions. Another is to break apart the rows and re-aggregate:

select pk,
       max(case when seqnum = 1 then dim end) as dim_1,
       max(case when seqnum = 2 then dim end) as dim_2,
       max(case when seqnum = 3 then dim end) as dim_3
from (select pk, row_number() over (order by dim desc) as seqnum
      from (select pk, length as dim from t
            union all
            select pk, width as dim1 from t
            union all
            select pk, height as dim1 from t
           ) t
     ) t
group by pk;

pk is a column that uniquely identifies each row (like a primary key).

Upvotes: 2

Related Questions