CODEWITHSUNDEEP
sqlpostgresql
Elif
Elif

Reputation: 31

How to get the 5 highest values for each row from 20 columns

I use postgresql. I know how to find the greatest value of multiple columns but I'm having a hard time trying to find the 5 MAX values between 20 columns. I have an identifier for each row called isbn.

I use this code to find the max value.

SELECT *,(SELECT MAX(price) FROM (VALUES(col_1),(col_2),(col_3),(col_4),(col_5),(col_6),(col_7),(col_8),(col_9),(col_10),(col_11),(col_12),(col_13),(col_14),(col_15),(col_16),(col_17),(col_18),(col_19),(col_20)) AS AllPrices(price)) AS max1 FROM my_table

For example the last 5 columns are the result that I want.

| col_1 | col_2 | col_3 | col_4 ..... col_20 *  max1 |  max2 |  max3 |  max4 |  max5  |
| ----- | ----- | ----- | -----        ----- * ----- | ----- | ----- | ----- | ------ | 
|   4   |   2   |   9   |  5    .....    13  *   13  |   9   |  5    |   4   |   2    | 
|   8   |   1   |   3   |  0    .....    2   *   8   |   3   |  2    |   1   |   0    |

Upvotes: 0

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If the columns are of the same time, I would suggest a lateral join to unpivot the data in each row and then using window functions and conditional aggregation:

select t.*, v.*
from t cross join lateral
     (select max(v.col) filter (where seqnum = 1) as max_col_1,
             max(v.col) filter (where seqnum = 2) as max_col_2,
             max(v.col) filter (where seqnum = 3) as max_col_3,
             max(v.col) filter (where seqnum = 4) as max_col_4,
             max(v.col) filter (where seqnum = 5) as max_col_5
      from (select v.col, row_number() over (order by col desc) as seqnum
            from (values (col_1), (col_2), . . .
                 ) v(col)
           ) v
      ) v;

Upvotes: 1

Related Questions