Get primary key of aggregated, group by SQL query using PostgreSQL

Question

Hi,

I'm struggling with creating a SQL query involving aggregates using PostgreSQL. Consider the following tables:

CREATE TABLE thing (
  id INT NOT NULL PRIMARY KEY,
  price NUMERIC(10,2) NOT NULL,
  description VARCHAR(255) NOT NULL,
  url VARCHAR(255) NOT NULL,
  location_id INT NOT NULL REFERENCES location(id)
)

CREATE TABLE location (
  id INT NOT NULL PRIMARY KEY,
  type INT NOT NULL,
  name VARCHAR(255) NOT NULL
)

Now, I would like to get all the thing records for each location with location.type = xxx that have the lowest price.

Something like:

SELECT min(price) FROM thing
INNER JOIN location ON (thing.location_id = location.id)
WHERE type = xxx
GROUP BY location_id

This will list me the lowest price for each location with type xxx, but how can I get the rows (or their primary keys) of these columns from table thing?

Answer 1

Use this PostgreSQL extension:

SELECT  DISTINCT ON (location.id) thing.*
FROM    location
JOIN    thing
ON      thing.location_id = location_id
WHERE   type = 1
ORDER BY
        location.id ASC, price ASC

This will select only the first row for each location.id.

Since your rows are sorted by location.id then by price, this will be the row with the minimal price.

In new PostgreSQL 8.4, you can also use window functions:

SELECT  *
FROM    (
        SELECT  thing.*, ROW_NUMBER() OVER (PARTITION BY location_id ORDER BY price) AS rn
        FROM    location
        JOIN    thing
        ON      thing.location_id = location_id
        WHERE   type = 1
        ) q
WHERE   rn = 1

Answer 2

Perhaps use a sub query

SELECT t.id,t.description,t.price FROM 
  ( SELECT location_id, min(price) FROM thing
      INNER JOIN location ON (thing.location_id = location.id)
      WHERE type = xxx
      GROUP BY location_id
   ) AS lowest
   INNER JOIN thing AS t
     ON t. location_id  = lowest.location_id;

Answer 3

I'm a SQL Server guy, but the following should be SQL-92 compliant and should work:

select th.*
 from thing th
  inner join (select lo.id, min(th.price) minPrice
               from location lo
                inner join thing th
                 on th.location_id = lo.id
               where lo.type = xxx
               group by lo.id) minSet
   on minSet.id = th.location_id
    and th.price = minSet.minPrice

Note also that I don't have table set up to test with, so there may be a typo or two in there.

While it does work, it sure looks awkward. If Postgres has anything like SQL's ranking functions, they'd make it a bit simpler.