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?

Solution 1

If you want to return the max value for each data_type in a separate column, then you should be able to use an aggregate function with a CASE expression:

select
  max(case when data_type='World of Warcraft' then value end) WorldofWarcraft,
  max(case when data_type='Quake 3' then value end) Quake3,
  max(case when data_type='Final Fantasy' then value end) FinalFantasy
from yourtable;

See SQL Fiddle with Demo

Solution 2

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.

Solution 3

If you want your data to be aggregated in single string, go with bluefeet example, if you need a recordset with a record for each type:

select
    data_type,
    max(value) as value
from table1
group by data_type

Comments

[user2146933]

Thank you it worked! I was using >max(case when data_type='World of Warcraft' then max(value) end) which didn't work, didn't think to get rid of the max in the inner part, thanks alot!

[Taryn]

@user2146933 Glad it worked. You were close, you can't nest aggregate functions like that. :)

[user2146933]

As a quick question and thought, would it be possible to do this based on the most RECENT entry as opposed to the maximum entry?

[Taryn]

@user2146933 How do you determine what is most recent? Do you have a datetime or id value? My suggestion is if you have different requirements to post a new question.

[user2146933]

there is a datetime column also associated with it, but if it is too much different i could ask on a different question

[Taryn]

@user2146933 I would post a new question with the details about your data and then the final result that you want, it would be easier then going back and forth in the comments. :)

[Erwin Brandstetter]

+1 Right on target. But guess what: I am suggesting crosstab() yet again. This time with a "1-size-fits-all" bullet. :)

[Taryn]

@ErwinBrandstetter Of course you come along with a crosstab answer! :) It was initially tagged with mysql as well so I gave a generic version.

[Erwin Brandstetter]

@bluefeet: .. and there's nothing wrong with it. All the more, since the OP is not all that clear what he needs exactly.

[Taryn]

@ErwinBrandstetter I guess I need to still try and spend some time learning some postgresql! :) But work is getting in my way.