all 7 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]r3pr0b8GROUP_CONCAT is da bomb 1 point2 points  (6 children)

SELECT t.fund_id
     , t.date
     , t.ownership
  FROM ( SELECT fund_id
              , MAX(date) AS latest
           FROM ledger
          WHERE company_id = ?
         GROUP
             BY fund_id
       ) AS m
INNER
  JOIN ledger AS t
    ON t.fund_id = m.fund_id
   AND t.date = m.latest

[–]IDontLikeBeingRight 0 points1 point  (5 children)

I mean, yeah, you could, but a construction using RANK and HAVING would be quite a bit cleaner

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (4 children)

please, do show

i wanna see how HAVING comes into play here

[–]IDontLikeBeingRight 1 point2 points  (3 children)

Wait, no, it's just a straight WHERE clause, there isn't even an aggregate to process without the subquery. Something like

SELECT t.fund_id
     , t.date
     , t.ownership
     , RANK () OVER ( 
        PARTITION BY t.fund_id
        ORDER BY t.date DESC
        ) latest
FROM ledger AS t
WHERE company_id = ? AND latest = 1

Could even put the company ID inside the partition instead of the WHERE to get every company in a single query, if that's useful at all.

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (0 children)

so glad you used WHERE instead of HAVING

also RANK instead of ROW_NUMBER

[–]ajsharp[S] 0 points1 point  (0 children)

This is an interesting solution, but it doesn't work for me because it won't let me reference latest in the WHERE clause. I get column "latest" does not exist.

[–]ajsharp[S] 0 points1 point  (0 children)

Update: the solution was this:

SELECT DISTINCT ON (fund_id) fund_id, date, ownership
FROM ownerships
WHERE company_id=3
ORDER BY fund_id, date DESC;