SQL - Using SELECT FROM x physical table

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Tue, 16 Feb 2010 11:45:17 -0200
Message-ID: <90b7e6bd1002160545v7612e20fx3108efa9a844c3e_at_mail.gmail.com>



Hi friends:

What is the best solution for the following problem:

I need to retrieve the max(year) from a column. This table will have around 500.000 lines. And then select all lines of this table where the date is equal to the max(year).
For example, I have lines for year 2005, 2006, 2007, 2008, 2009. The max year is 2009.
So I need retrieve only lines for 2009.

Solution 1:

Select table1.col1, table1.col2, table1.year

   from table table1 ,

          (SELECT MAX(year) year
                 FROM table) table2

 where table1.year = table2.year;

Solution 2:
2.1 )Create a table tablex like that
create table tablex
 (max_year number(4))
2.2) Write in the max_year the max(year) using trigger, pl/sql whatever I use for it I will have the max_year filled as 2009 in unique line and then do it:

Select table1.col1, table1.col2, table1.year

   from table table1 ,

          tablex
  where table1.year = tablex.max_year;

Regards
Eriovaldo

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 16 2010 - 07:45:17 CST

Original text of this message