EXPERT RESPONSE
In SQL FAQ: Common SQL questions, part 3 (05 July 2007), under the heading
Latest X for each Y, are links to previous
questions where we've discussed this before. In fact, there's
even one called
Latest row for each group (17 December 2003). We'll use the same
technique here. The "groups" will be the transactions for each month.
select TransDate
from Transactions T
where TransDate
= ( select max(TransDate)
from Transactions
where extract(year_month from TransDate)
= extract(year_month from T.TransDate)
)
Wait a moment, isn't there an easier way? Strictly speaking, yes. We
could write:
select max(TransDate)
from Transactions
group
by extract(year_month from TransDate)
But this only works if the last transaction date per month
is the only column we want returned. (And how useful is that?)
Add any other column and we need the first query.
An alternate method exists which replaces the correlated
subquery with a join to the derived table (an inline subquery)
produced by the GROUP BY query.
|
