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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
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
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading