Re: SQL - Using SELECT FROM x physical table
- From: Igor Neyman <igor.neyman@xxxxxxxxx>
- To: ecandrietta@xxxxxxxxx
- Date: Tue, 16 Feb 2010 09:01:20 -0500
SELECT col1, col2, year
FROM table
WHERE year = (SELECT MAX(year) FROM table);
Igor Neyman
On Tue, Feb 16, 2010 at 8:45 AM, Eriovaldo Andrietta
<ecandrietta@xxxxxxxxx>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
>
>
>
>
>
>
Other related posts:
