SQL MAX of multiple columns and retrieve each row

Question

I want to get the MAX value of close to 20 fields in my database. My issue is that I also want some other fields with all of these MAX values. I'm using PostgreSQL.

Here is an example:

Match Table definition

CREATE TABLE "matches" (
    "id" int4 NOT NULL DEFAULT nextval('match_players_id_seq'::regclass),
    "kills" int4 NOT NULL,
    "deaths" int4 NOT NULL,
    "assists" int4 NOT NULL,
    "gamemode" int4 NOT NULL,
    PRIMARY KEY ("id")
);

Match table example data

id	kills	deaths	assists	gamemode
1	0	0	3	1
2	1	0	2	2
3	1	12	0	3
4	7	2	27	1
5	1	4	27	2
6	2	3	1	3

INSERT INTO "matches" ("id", "kills", "deaths", "assists", "gamemode") VALUES
(1, 0, 0, 3, 1),
(2, 1, 0, 2, 2),
(3, 1, 12, 0, 3),
(4, 7, 2, 27, 1),
(5, 1, 4, 27, 2),
(6, 2, 3, 1, 3);

Result I want (Table or JSON style) from the above data

what	amount	gamemode	id
"kills"	7	1	4
"deaths"	12	3	3
"assists"	27	1	4

{
  "maxKills": {"id": 4, "kills": 7, "gamemode": 1},
  "maxDeaths": {"id": 3, "deaths": 12, "gamemode": 3}
  "maxAssists": {"id": 4, "assists": 27, "gamemode": 1} // Get the first one if 2 are equal
}

This is simplified. Here is a little pastebin to give you an idea of the real query: https://pastebin.com/eT8MNKKe
And another pastebin with the @Erwin answer's implementation: https://pastebin.com/2B8imJTj

I already posted this question two years ago but I used a NoSQL database (Mongo): Get objects containing max values for multiple fields using aggregation in mongodb

Here is my idea, I don't know if it's the best one:
Do 2 queries. The first one to get the MAX value of the 20 columns, and the second one to fetch the rows with a WHERE clause on the 20 aggregated MAX values. I'm just not sure how to do when multiple rows have the same MAX value.

Answer 1

You want to get the rows with the maximum values. A SQL result is a table consisting of columns and rows. This is a pure SQL approach:

select what, amount, gamemode, id
from
(
  select id, gamemode, 'kills' as what, kills as amount, max(kills) over () as max_amount from mytable
  union all
  select id, gamemode, 'deaths' as what, deaths as amount, max(deaths) over () as max_amount from mytable
  union all
  select id, gamemode, 'assists' as what, assists as amount, max(assists) over () as max_amount from mytable
  union all
  ...
) all_candidates
where amount = max_amount;

There may be some JSON function build in to convert this result to JSON. I don't know. Maybe others can answer this.

Answer 2

Perfect. You can use window version of max as I suspected. The only real difficulty was devising something to partition it on and not get the value for each row.

select m.*
     , max(m.kills )    over () max_kills
     , max(m.deaths )   over () max_deaths
     , max(m.assists )  over () max_assists
     , max(m.gamemode ) over () max_gamemode  
 from matches m; 

See demo. For demo I added a couple of columns just to show the inclusion of 'other columns', but did not populate them.

NOTE: Not directly related but you should avoid double quotes (") on database names. Once used they must always be used. It is just not worth the effort.

Answer 3

This should be fastest and simplest:

(SELECT 'kills' AS what, kills, gamemode, id   FROM matches ORDER BY kills DESC, id LIMIT 1)
UNION ALL
(SELECT 'deaths'       , deaths, gamemode, id  FROM matches ORDER BY deaths DESC, id LIMIT 1)
UNION ALL
(SELECT 'assists'      , assists, gamemode, id FROM matches ORDER BY assists DESC, id LIMIT 1)
--  more ...

db<>fiddle here

Add id as second ORDER BY expression. This way, if multiple rows tie for the highest score, the row with the smallest id is chosen.

All parentheses are required. See:

• Combining 3 SELECT statements to output 1 table
• Create a unique index on a non-unique column

If any of the ORDER BY columns can be NULL, add NULLS LAST. See:

• Sort by column ASC, but NULL values first?