Need to retrieve Max 3 dates

[jasonhuibers]

Here is a sample of my query... It takes forever to run...

I have a list of records like this:

Column A Column B
12/25/2011 Stacey
12/01/2010 Brian
01/01/2009 John
10/10/2009 Steve
09/25/2011 Tim

I want to take the 3 max dates and values, the results to be:
12/25/2011 Stacey
09/25/2011 Tim
12/01/2010 Brian

SELECT READ_DATE1, READ_DATE2, READ_DATE3 FROM
Table1,Table2,Table3
WHERETable1.ID = Table2.ID
AND Table2.ID = Table3.ID
AND and Table1.READ_DATE1 = (select max(READ_DATE) from Table3
where Table3.ID = Table1.ID)
AND Table2.READ_DATE2 = (select max(READ_DATE) from Table4
where Table4.ID = Table2.ID
and READ_DTTM < Table1.READ_DATE1 )
AND and Table3.READ_DATE1 = (select max(READ_DATE) from Table5 where Table5.ID = Table1.ID and READ_DTTM < Table2.READ_DATE1 )

 

[Turkbear]

Hi,
How many tables are involved?
Can you create a view ( using UNION ) to have only one source for the data?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jasonhuibers]

9 tables - can you please elaborate

 

[Turkbear]

Hi,
If all the tables have the same fields and datatypes
you can create a UNION of them into a View something
like

Create View All_Date_Tables as
Select ColumnA,ColumnB
from Table1
UNION
Select ColumnA,ColumnB
from Table2
UNION
Select ColumnA,ColumnB
from Table3......etc

Then your query only has to reference 1 object:

Select * from
(Select ColumnA,ColumnB from View All_Date_Tables
ORDER BY ColumnA DESC)
Where rownum < 4;

Check your Oracle docs for more info on UNION and also on the use of the Select statement as a data source.







To Paraphrase:"The Help you get is proportional to the Help you give.."