Re: SQL - Using SELECT FROM x physical table

From: Alessandro Lia <a.lia_at_tct-it.com>
Date: Tue, 16 Feb 2010 14:59:58 +0100
Message-ID: <002001caaf10$532f5970$130210ac_at_CPU002019>

For me you could use the following:

select table1.col1, table1.col2, table1.year   from table1
 where table1.year=(select max(year) from table1);

I'm not sure I get your problem, but this should be good. let me know

Alessandro

• Original Message ----- From: Eriovaldo Andrietta To: ORACLE-L Sent: Tuesday, February 16, 2010 2:45 PM Subject: SQL - Using SELECT FROM x physical table

  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:59:58 CST