Comment by cyberax | Hacker News Reader

Comment by cyberax | Hacker News Reader

Parent Full thread

A very simple problem, you have a table of house sales. For each house (identified by an ID), you want to find the sale date that had the lowest price.

You'd think that you should be able to sort by the house ID, then by the sale price, then group by the house ID, and select the sale _date_ from the first row of each group.

Something like: `select s.house_id, first(s.date) from sale s order by s.house_id, s.price asc group by s.house_id`. But this is impossible, because there's no `first` function in SQL.

But nope, you need DB-specific extensions for that.

What do you want to see if there's more than one sale date with the lowest price?

In any case, you can do this with a subquery and join it with the main table. I don't think there's anything non-standard about it:

  SELECT hs.*
  FROM house_sales hs
  JOIN (
      SELECT house_id, MIN(price) AS min_price
      FROM house_sales
      GROUP BY house_id
  ) min_prices
  ON hs.house_id = min_prices.house_id AND hs.price = min_prices.min_price

I'm sure you can do it with window functions as well.

> What do you want to see if there's more than one sale date with the lowest price?

Either one is fine.

PostgreSQL has an extension (DISTINCT ON) that allows to do what I want without subqueries. But standard SQL is simply deficient in this regard, it should be straightforward but it's not.

Reply on news.ycombinator.com