0

Greatest value of multiple columns with column name?

I was reading the question above (link above) and the "ACCEPTED" answer (which seems correct) and have several questions concerning this answer.

(Sorry I have to create a new post, don't have a high enough reputation to comment on the old post as it seems very old)

Questions
My first question is what is the significance of "@var_max_val:= "? I reran the query without it and everything ran fine.

My second question is can someone explain how this achieve it's desired result: 

 CASE @var_max_val WHEN col1 THEN 'col1'
                     WHEN col2 THEN 'col2'
                     ...
   END AS max_value_column_name

My third question is as follows:

It seems that in this "case" statement he manually has to write a line of code ("when x then y") for every column in the table. This is fine if you have 1-5 columns. But what if you had 10,000? How would you go about it?

PS: I might be violating some forum rules in this post, do let me know if I am. Thank you for reading, and thank you for your time!

Community
  • 1
  • 1
OctaveParango
  • 113
  • 1
  • 14

2 Answers2

5

The linked question is about mysql so it does not apply to postgresql (e.g. the @var_max_val syntax is specific to mysql). To accomplish the same thing in postgresql you can use a LATERAL subquery. For example, suppose that you have the following table and sample data:

CREATE TABLE t(col1 int, col2 int, col3 int);
INSERT INTO t VALUES (1,2,3), (5,8,6);

Then you can identify the maximum column for each row with the following query:

SELECT *
FROM t, LATERAL (
            VALUES ('col1',col1),('col2',col2),('col3',col3)
            ORDER BY 2 DESC
            LIMIT 1
        ) l(maxcolname, maxcolval);

which produces the following output:

 col1 | col2 | col3 | maxcolname | maxcolval 
------+------+------+------------+-----------
    1 |    2 |    3 | col3       |         3
    5 |    8 |    6 | col2       |         8

I think this solution is much more elegant than the one presented in the linked article for mysql.

As for having to manually write the code, unfortunately, I do not think you can avoid that.

redneb
  • 21,794
  • 6
  • 42
  • 54
  • just a quick note, this ` SELECT GREATEST(col1, col2, col3, ...) AS max_value, CASE WHEN col1 THEN 'col1' WHEN col2 THEN 'col2' ... END AS max_value_column_name FROM table_name WHERE ...` works in postgres – OctaveParango Sep 08 '16 at 19:38
  • 1
    The `GREATEST` function does exists in postgresql, but the linked article also uses user defined variables that are specific to mysql. – redneb Sep 08 '16 at 19:41
3

In Postgres 9.5 you can use jsonb functions to get column names. In this case you do not have to write manually all the columns names. The solution needs a primary key (or a unique column) for proper grouping:

create table a_table(id serial primary key, col1 int, col2 int, col3 int);
insert into a_table (col1, col2, col3) values (1,2,3), (5,8,6);

select distinct on(id) id, key, value
from a_table t, jsonb_each(to_jsonb(t))
where key <> 'id'
order by id, value desc;

 id | key  | value 
----+------+-------
  1 | col3 | 3
  2 | col2 | 8
(2 rows)
klin
  • 112,967
  • 15
  • 204
  • 232