Optimal Date selection


Optimal Date selection

Hi there! This is my first post so go easy!

Where I work we have several tables which are run periodically (in this case monthly).

I'm attempting to select information which comes from both the most recent and the second most recent entries, which occur on the last WORKING day of the month.

 The common practice seems to be essentially join tables and then use code such as

cus.period_dte/100 = add_months(appl_dte,-1)/100

for example. I however decided today to try something different. Namely :

SELECT period_dte FROM y
WHERE y.period_dte = (SELECT MAX(period_dte) FROM x WHERE
period_dte <> (SELECT MAX(period_dte) FROM x))

Now both methods work. And I understand how and why. My only question is which is more effecient? The computation or the sub query?



Re: Optimal Date selection

The most reliable way would be to check the EXPLAIN plan of both the queries. But I have seen instances where the joining tables SQL run more efficiently than the subqueries.

Re: Optimal Date selection

Yeah thanks. I have run an explain on both queries but as the tables (in this instance) are quiet small both are coming back at less than .1 of a second.. My question is more of a general one as some of the tables I'm working with have several million rows..