Re: Max

>
> Hello,
>
> I have :
>
> TX Date
> -- -----
> 3 2002-01-01
> 4 2002-05-01
> 5 2002-03-01
>
> I would like to get TX value where Date is the max date (4 in this sample)
> without using order by desc and selecting the first line...
>
> Thanks in advance.

Use row_number from analytical functions:

set pagesize 500
set feedback off
alter session set nls_date_format = 'DD.MM.YYYY';

create table foo_ (
  id number,
  tx number,
  dt date
);

insert into foo_ values (1, 3, '01.01.2002');
insert into foo_ values (1, 4, '05.01.2002');
insert into foo_ values (1, 5, '03.01.2002');
insert into foo_ values (1, 7, '01.01.2002');

insert into foo_ values (2, 1, '02.01.2002'); insert into foo_ values (2, 8, '02.01.2002');

select tx from (
  select
    tx, row_number() over (order by dt desc) r   from
    foo_
)
where r=1;

Using analytical functions in this case causes only one full table scan:

SELECT STATEMENT ()
 VIEW ()
  WINDOW (SORT PUSHED RANK)
   TABLE ACCESS (FULL) FOO_ while a

select tx from foo_ where dt = (select max(dt) from foo_);

causes two full table scans:

SELECT STATEMENT ()
 FILTER ()
  TABLE ACCESS (FULL) FOO_
  SORT (AGGREGATE)
   TABLE ACCESS (FULL) FOO_   hth
  Rene Nyffenegger

-- 
  no sig today