Q
Problem solve Get help with specific problems with your technologies, process and projects.

The last transaction date every month

SQL expert Rudy Limeback explains how to display the last date of the record of every month.

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.

This was last published in February 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close