PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL: group by to select max observation

Imagine you have the following table named city:

market product cost
A 1 54
A 2 62
A 3 38
B 1 12
B 2 12
B 3 5

I need the most expensive product for each market, i.e.:

market product cost
A 2 62
B 1 12
B 2 12

I know this SQL query doesn’t work:

SELECT *, MAX(cost)
FROM city
GROUP BY market

The point isn’t finding a solution (that’s pretty easy I think).

My question: is there a specific reason why the SQL language doesn’t evolve to allow the query above to find the result?

Tags:

2

Answers


  1. 0

    if you are getting error ‘city.idt’ is invalid…
    it is because when you are using MAX or SUM or other similar functions in sql
    you should specify other coloumns that you want in group by
    for example

    SELECT MAX(cost) as cost, market FROM city GROUP BY market

    so there we want market so we should specify it in group by
    the result will be
    cost market
    64 A
    12 B
    if you want to get other columns without specifying them in the group by:

    SELECT
    *
    FROM
    city
    WHERE
    cost IN
    (
    SELECT
    DISTINCT
    MAX(cost)
    FROM
    city
    GROUP BY
    market
    )
    ORDER BY
    market ASC

    Login or Signup to reply.
  2. 0

    You can use a subselect or a CTE to get the results. First get the maximum price in the market, and then list the products

    Here you have the query with the subselect:

    SELECT c.* 
FROM city c 
JOIN (select market, max(cost) as maxcost From city group by 1) s 
ON c.market=s.market AND c.cost = s.maxcost;

    And using CTE:

    with maximumCost as (
select market, max(cost) as maxcost From city group by 1)
SELECT c.* 
from city c 
JOIN maximumCost m ON c.market=m.market AND c.cost=m.maxcost;

    In both cases you get the desired result:

     market | product | cost
--------+---------+------
 A      |       2 |   62
 B      |       1 |   12
 B      |       2 |   12
(3 rows)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search