Re: SQL - Using SELECT FROM x physical table

Thanks for answering.
And I am sorry for do not explain the issue correctly. The question is:

I need identify lines where the year is equal to max(year) using the same column in the same table.

Ex. I have a table and it has a column (called year) with datatype DATE dd/mm/yyyy

      Inside this table there are several years 2005, 2006, 2007, 2008 and 2009.

      In the future it will have 2010, 2011 ...
      and the max(year) will change.
      today the last year is 2009.
      so I need a column in the view to identify that the line refers to the

So,

I need a column in a views that represents the max(year). I need to use a decode.

then, the query will be like that:

select col1, col2, col3 ,
  DECODE(TO_NUMBER(TO_CHAR(table.year, 'YYYY')), table_max.max_year, 1, 0) last_year
from table,

      (SELECT MAX(TO_NUMBER(TO_CHAR(table.year, 'YYYY'))) max_year
           FROM table
        ) table_max

• here I did a select max(year) in the from and dont have a physical table

or

Select col1, col2, col3,
 DECODE(TO_NUMBER(TO_CHAR(table.year, 'YYYY')), tablex.max_year, 1, 0) last_year

   from table table1 ,

          tablex

• tablex is physical table that contains only a column max_year filled with the last_year = 2009, in this test case.

I did some tests and the second option seems better looking for performance , but it is bad for control, because must have a process to write the last year on the table tablex.

I think that I don´t need index because I won´t use it in the where clause.I will use only in DECODE.

Thanks and Regards
Eriovaldo

On Tue, Feb 16, 2010 at 12:48 PM, Holvoet, Jo <jo.holvoet_at_thomascook.be>wrote:

> In that case, using the rank() analytical function could get rid of one
> of the table scans :
>
> Select col1, col2, ... from (
> Select col1, col2, ... , rank() over (order by year desc) r
> From table
> )
> Where r = 1
>
> mvg,
> Jo
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Daniel Fink
> Sent: dinsdag 16 februari 2010 15:26
> To: ecandrietta_at_gmail.com
> Cc: ORACLE-L
> Subject: Re: SQL - Using SELECT FROM x physical table
>
>
> Is there an index on year for the table? If so, then getting MAX(year)
> and filtering on year should be relatively painless. If not, you would
> perform two full table scans on the table.
>
> Using a trigger means executing it every time there is an insert. That
> is likely more overhead in the processing of the trigger than with an
> additional index.
>
> --
> Daniel Fink
>
> OptimalDBA http://www.optimaldba.com
> Oracle Blog http://optimaldba.blogspot.com
>
> Lost Data? http://www.ora600.be/
>
>
>
> Eriovaldo Andrietta wrote:
> > 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l