current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
1  
So what's this got to do with MySQL? –  Strawberry Aug 28 '13 at 17:50
    
Your presented preliminary solution is a single statement. You mean a single result row? If so, do you have a fixed number of 3 types, or are there more, possibly dynamic? –  Erwin Brandstetter Aug 28 '13 at 21:54

3 Answers 3

up vote 4 down vote accepted

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

share|improve this answer
    
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! –  user2146933 Aug 28 '13 at 18:13
    
@user2146933 Glad it worked. You were close, you can't nest aggregate functions like that. :) –  bluefeet♦ Aug 28 '13 at 18:16
    
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? –  user2146933 Aug 28 '13 at 18:27
    
@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. –  bluefeet♦ Aug 28 '13 at 18:29
1  
@bluefeet: .. and there's nothing wrong with it. All the more, since the OP is not all that clear what he needs exactly. –  Erwin Brandstetter Aug 28 '13 at 22:52
up vote 3 down vote

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.

share|improve this answer
    
+1 as always for your very detailed answer! –  bluefeet♦ Aug 28 '13 at 23:02
    
+1 good one, though I doubt OP will use this :) –  Roman Pekar Aug 29 '13 at 7:00
up vote 1 down vote

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.