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.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading