RE: SQL - Using SELECT FROM x physical table

select a.col1, col2, (select to_char(max(date_column),'YYYY') from table b where b.col1 = a.col1) from table a;    

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eriovaldo Andrietta Sent: Tuesday, February 16, 2010 12:58 PM To: jo.holvoet_at_thomascook.be
Cc: ORACLE-L
Subject: Re: SQL - Using SELECT FROM x physical table

Hi All,

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 last year.

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