SQL - Using SELECT FROM x physical table
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-lReceived on Tue Feb 16 2010 - 07:45:17 CST