postgresql - Selecting multiple max() values using a single SQL statement

Question

I have a table that has data that looks something like this:

data_type, value
World of Warcraft, 500
Quake 3, 1500
Quake 3, 1400
World of Warcraft, 1200
Final Fantasy, 100
Final Fantasy, 500

What I want to do is select the maximum of each of these values in a single statement. I know I can easily do something like

select data_type, max(value)
from table
where data_type = [insert each data type here for separate queries]
group by data_type

But what I want it to display is is

select data_type, 
  max(value) as 'World of Warcraft', 
  max(value) as 'Quake 3', 
  max(value) as 'Final Fantasy'

So I get the max value of each of these in a single statement. How would I go about doing this?

Answer 1

Once again, for more than just a few "data types", I suggest to use crosstab():

SELECT * FROM crosstab(
     $$SELECT DISTINCT ON (1, 2)
              'max' AS "type", data_type, val
       FROM   tbl
       ORDER  BY 1, 2, val DESC$$

    ,$$VALUES ('Final Fantasy'), ('Quake 3'), ('World of Warcraft')$$)
AS x ("type" text, "Final Fantasy" int, "Quake 3" int, "World of Warcraft" int)

Returns:

type | Final Fantasy | Quake 3 | World of Warcraft
-----+---------------+---------+-------------------
max  | 500           | 1500    |    1200

More explanation for the basics:
PostgreSQL Crosstab Query

Dynamic solution

The tricky thing is to make this completely dynamic: to make it work for

• an unknown number of columns (data_types in this case)
• with unknown names (data_types again)

At least the type is well known: integer in this case.

In short: that's not possible with current PostgreSQL (including 9.3). There are approximations with polymorphic types and ways to circumvent the restrictions with arrays or hstore types. May be good enough for you. But it's strictly not possible to get the result with individual columns in a single SQL query. SQL is very rigid about types and wants to know what to expect back.

However, it can be done with two queries. The first one builds the actual query to use. Building on the above simple case:

SELECT $f$SELECT * FROM crosstab(
     $$SELECT DISTINCT ON (1, 2)
              'max' AS "type", data_type, val
       FROM   tbl
       ORDER  BY 1, 2, val DESC$$

    ,$$VALUES ($f$     || string_agg(quote_literal(data_type), '), (') || $f$)$$)
AS x ("type" text, $f$ || string_agg(quote_ident(data_type), ' int, ') || ' int)'
FROM  (SELECT DISTINCT data_type FROM tbl) x

This generates the query you actually need. Run the second one inside the same transaction to avoid concurrency issues.

Note the strategic use of quote_literal() and quote_ident() to sanitize all kinds of illegal (for columns) names and prevent SQL injection.

Don't get confused by multiple layers of dollar-quoting. That's necessary for building dynamic queries. I put it as simple as possible.