Here's essentially what I'm trying to do:

    retailers.title AS retailer_title, 
    ST_Distance(retailers.lonlat, 'SRID=4326;POINT(-118.4104684 34.1030032)' :: geography, 15 * 1609.34) AS distance 
FROM deals,
INNER JOIN deal_retailers ON deal_retailers.deal_id = deals.id
INNER JOIN retailers ON retailers.id = deal_retailers.retailer_id
WHERE retailers.status = 2
GROUP BY deals.id
ORDER BY deals.discount_percent

I can't seem to find any combination of sub queries or window functions where I can meet all the goals: get the distance/title, only show one row per deal id, and order by deals.discount percent

I'm definitely not fully understanding how the group/distinct/joins all work together. I'm on Postgres 14 if that makes any difference

  • You might be looking for distinct on (...)
    – user1822
    Commented Jan 31, 2023 at 8:53
  • @a_horse_with_no_name that gives me distinct, but then I have to sort by id instead of by discount_percent Commented Jan 31, 2023 at 14:59
  • You can still sort by discount_percent: order by id, discount_percent
    – user1822
    Commented Jan 31, 2023 at 15:05
  • Which one row per deal id do you want to show?
    – jjanes
    Commented Jan 31, 2023 at 15:27
  • You can have multiple ORDER BYs. select * from (select distinct on ... ORDER BY colA, colZ desc) foo order by colB
    – jjanes
    Commented Jan 31, 2023 at 15:30


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.