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

Question

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