No LAST function 2

[beansoup]

I have a table as such:
id, apartment, date, description, amount

I'd like to query it as follows:

select apartment, sum(amount) as balance, max(date), last(description)
from mytable
group by apartment

Of course there is no "last" function. I'm after a list of current balances by apartment showing the last transaction date and description.

Any suggestions?

 

[SimonSellick]

Assuming ids always increase (so the highest date has the highest id), you can use something along these lines:

SELECT
  t.apartment
, x.balance
, x.latest_date
, t.description
FROM (  
  SELECT 
    apartment
  , sum(amount) balance
  , max(date) latest_date
  , max(id) latest_id
  FROM tbl
  GROUP BY
    apartment
) x 
JOIN tbl t ON t.id = x.latest_id

There is probably a tidier way to express the same using a Common Table Expression (CTE) but it's too early in the morning for me.

If the id does not always increase but the dates are unique within apartment, you can use the date as the JOIN column.

 

[PWise]

what will be if a transaction from a prior date was entered after a transaction of a later date


Try

select apartment,  balance, mdate, description
from mytable
inner join (Select apartment  max(date) mdate ,sum(amount),balance
            From mytable
            Group by apartment )maxdate
on maxdate.apartment =mytable.apartment 
And maxdate.mdate=mytable.date

 

[beansoup]

I find (with slight modification to Pwise's solution) that both work, but Pwise's solution gives me more than one result per apartment if there is more than one transaction on the latest date (which there sometimes is).

Anyway, thanks to both of you - I imagine this is "child's play" to both of you, but it's "magic" to me!

 

[markros]

If you want to learn more, check these blog posts

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library