RE: SQL - Using SELECT FROM x physical table
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <ecandrietta@xxxxxxxxx>, <jo.holvoet@xxxxxxxxxxxxx>
- Date: Tue, 16 Feb 2010 13:03:22 -0500
Try
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Eriovaldo Andrietta
Sent: Tuesday, February 16, 2010 12:58 PM
To: jo.holvoet@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Daniel Fink
Sent: dinsdag 16 februari 2010 15:26
To: ecandrietta@xxxxxxxxx
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
>
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
