oracle - Select first, second and third greatest numbers over multple columns

Question

I have this table structure

CLIENT_ID | QTY_A | QTY_B | QTY_C | QTY_D | QTY_E 
====================================================
1         |     20|     21|     19|   NULL|    30 
----------------------------------------------------
2         |     45|   NULL|     31|     80|    54 
----------------------------------------------------

And i am trying to calculate, for wach CLIENT_ID, the first, second and third largest values.

How can i achieve this? Analytic functions cannot help me here, right? I cannot just order by a column.

Thanks in advance.

If `QTY_A` and `QTY_B` columns have equal largest values, then you need both of them? or unique? — Oto Shavadze, Apr 11 '17 at 09:58

score 0 · Answer 1 · answered Apr 11 '17 at 09:51

Use UNPIVOT to convert the columns to rows and then the ROW_NUMBER analytic function to number the rows:

SELECT client_id,
       type,
       quantity
FROM   (
  SELECT client_id,
         type,
         quantity,
         ROW_NUMBER() OVER ( PARTITION BY client_id ORDER BY quantity DESC ) AS rn
  FROM   table_name
  UNPIVOT ( quantity FOR type IN ( qty_a, qty_b, qty_c, qty_d, qty_e ) )
)
WHERE  rn <= 3

oracle - Select first, second and third greatest numbers over multple columns

1 Answers1