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?

Quassnoi · Accepted Answer · 2009-07-28 14:50:55Z

5

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

edited Jul 28, 2009 at 14:50

answered Jul 28, 2009 at 14:45

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Haes Over a year ago

perfect, that's exactly what I was looking for.

nos · Accepted Answer · 2009-07-28 14:48:20Z

0

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;

answered Jul 28, 2009 at 14:48

nos

231k60 gold badges436 silver badges516 bronze badges

Comments

Philip Kelley · Accepted Answer · 2009-07-28 14:50:04Z

0

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.

answered Jul 28, 2009 at 14:50

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

2 Comments

Haes Over a year ago

That's also what I came up with, originally. The problem with this query is, that it will return multiple thing columns for a location if the lowest price is not unique.

Philip Kelley Over a year ago

Based on the description, I thought that was what you were looking for. If there are duplicate things based on minimum price, which would you want to select? (Rhetorical question, since you already have an answer -- row_number() is a very useful extension.)

sree · Accepted Answer · 2015-02-09 12:24:09Z

0

Try this query

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

answered Feb 9, 2015 at 12:24

sree

1,9701 gold badge22 silver badges37 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related