News for hackers. Stuff that matters.
Re:SQL or Death? Seminar Series – Spring 2025 – Carnegie Mel... by kmoser on Sunday February 02, 2025 @05:59PM (#42912762)
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.
Re:SQL or Death? Seminar Series – Spring 2025 – Carnegie Mel... by cyberax on Sunday February 02, 2025 @07:19PM (#42913341)
> 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.