Ask The Oracle Expert: Questions & Answers

The last transaction date every month

The last transaction date every month

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

I need to display the last date of the record of every month. For example:

2007-12-24
2007-12-21
2007-11-21
2007-11-20
2007-11-15
2006-01-01

I need the output as:

2007-12-24
2007-11-21
2006-01-01

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.