Categories
Development

What is the simplest way to get the least value and second least value from multiple columns (MIN function does not work this)?

Below table is from Teradata database

SELECT
  sku.Item_id,
  sku.Item_length,
  sku.Item_width,
  sku.Item_heigth,
FROM  Category_item sku

Item_id | Item_length | Item_width |Item_heigth
-------------------------------------------
104174        8            6           1
9482763       8            8           8
8434610       8            9           1
2109145       54           34          2
567106        41           41          2
2028731       3            4           3

The final result that I want is,

 Item_id | Item_length | Item_width |Item_heigth | MinValue | SecondMinVale
    ---------------------------------------------------------------------------
    104174        8            6           1          1            6
    9482763       8            8           8          8            8
    8434610       8            9           1          1            8
    2109145       54           34          2          2            34
    567106        41           41          2          2            41
    2028731       3            4           3          3            3

So I did a little research online, most of them used MIN function with a subquery or raw over by partition. Min function does not work for me, because I have three separate columns. I want to get minimum or least two values from the above three columns. 

I tried to do subquery or CTE using Least function, but I am stuck with getting the second least value. I am not sure how to use raw over partition for this.

Any help regarding this will be highly appreciated.

Thank you so much!!

Leave a Reply

Your email address will not be published. Required fields are marked *