Re: [Beginner] Problem with Order by of a date

On 2005-11-08, fmarchioni_at_libero.it <fmarchioni_at_libero.it> wrote:
> Hi all,
> I'm building a query where I need to retrieve just 1 one row,
> which must be that with the highest Date (I mean that which
> is closest to the moment where the query is issued)
>
> SELECT a.lista_campi_tpr, date_import
> FROM CELL_PR cp
> WHERE cp.id_cella = 'PZ81U1'
> AND ROWNUM < 2
> ORDER BY date_import DESC
>
> The Date is date_import.......
> unfortunately that doesn't work..........
> anybody can help me ?
> Thanks

Francesco,
this is a perfect fit for analytic functions:

create table bla_bla (
  a date,
  b number
);

begin
  for i in 1 .. 10 loop
    insert into bla_bla values (trunc(sysdate+i), mod(i*11,13));   end loop;
end;
/

• Wrong
• =====

select a, b from bla_bla
 where rownum = 1
 order by a;

--
-- This is wrong because rownum is evaluated before the order by.
-- Essentially, it selects ONE (more or less) random row from
-- the table and orders it by a. 

-- Correct
-- =======
select a, b from (
  select
    row_number() over (order by a desc) r, a, b 
  from bla_bla
)
where r=1;

-- This is better because in the inner select, it
-- orders a descending and assigns a value to r.
-- This value starts with 1 and is incremented by
-- 1 for each row.
-- The outer select then selects the (desired) row
-- whose r = 1.


hth
Rene


-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/