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

The row with the latest date

I have a database of millions of rows of trades. I want to retrieve the trade record of the latest buy trade. This would have the latest TRADE_DATE and the BSCODE = 'B.' How do I specify the latest TRADE_DATE?

I have a database of millions of rows of trades. I want to retrieve the trade record of the latest buy trade. This...

would have the latest TRADE_DATE and the BSCODE = 'B.' How do I specify the latest TRADE_DATE?

The term "latest" when applied to dates means the highest date. To use this in a query usually requires the MAX function in a subquery:

select trade_date
     , trade_amount
     , other_columns
  from trades
 where trade_date
     = ( select max(trade_date)
           from trades
          where bscode = 'B' ) 
   and bscode = 'B' 

Note that we need to test for the BSCODE in both the main query and the subquery. It's possible that there could be no B codes on the last date, so if we omitted that condition in the subquery, we'd still get the latest date from the subquery, but then the outer query would return nothing, since it's testing for B codes on that date. Similarly, if we include the condition in the subquery but omit it from the main query, the subquery would return the latest date for a buy code, but the main query would return other transactions for that date as well.

This was last published in September 2005

Dig Deeper on Oracle and SQL

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