Get the primary key of an aggregated, batch SQL query using PostgreSQL

Question

I am struggling with creating an SQL query using 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 record entries for each location using location.type = xxx, which have the lowest price.

Sort of:

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

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

Answer 1

Use this extension PostgreSQL:

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.

location.id, price, .

PostgreSQL 8.4 :

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

select thing.id,thing.description,thing.url,low.location_id,low.minimum from
(select thing.location_id,min(price) as minimum from thing
 join location on thing.location_id=location.id
  where location.type = 13 group by location_id) as low
   inner join thing on thing.location_id = low.location_id

Answer 3

Maybe use an auxiliary 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 4

I am a SQL Server guy, but the following should be compatible with SQL-92 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

Please also note that I do not have a table to check with, so there may be a typo or two.

While it works, it looks uncomfortable. If Postgres has something like SQL ranking functions, they will make it a little easier.