Best IT Recipes

Select multiple max () values ​​using a single SQL statement

I have a table with 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 one expression. I know that 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 I want it to display:

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

So, I get the maximum value of each of them in one expression. How can i do this?

+2
sql postgresql pivot crosstab
Aug 28 '13 at 17:48
source share
3 answers

If you want to return the maximum value for each data type in a separate column, you should be able to use the 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

+4
Aug 28 '13 at 17:52
source share

And again, for more than a few “data types," I suggest using 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)

Return:

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

More explanation for the basics:
PostgreSQL Cross Forward Request

Dynamic solution

The difficulty is to make it fully dynamic: for it to work for

  • 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: this is not possible with the current PostgreSQL (including 9.3). There are approximations with polymorphic types and ways to circumvent restrictions using arrays or hstore types . Maybe enough for you. But it is strictly impossible to get the result with separate columns in a single SQL query. SQL is very hard on types and wants to know what to expect.

However , this can be done with two queries. The first builds the actual request for use. Based 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 that you really need. Run the second inside the same transaction to avoid concurrency issues.

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

Do not confuse multiple dollar-quoting layers . This is necessary to create dynamic queries. I put it as simple as possible.

+5
Aug 28 '13 at 22:44
source share

If you want your data to be combined into one line, use the bluefeet example if you need a set of records with a record for each type:

 select data_type, max(value) as value from table1 group by data_type
+1
Aug 28 '13 at 18:02
source share


More articles:


All Articles