 |
use of ROWNUM
Then I have this query result: >select * from analisi_ricovero WHERE id_ric=82
ID_ANL ID_RIC INDATA VALORE 41 82 29-APR-07 11.54.17,000000 PM 43 41 82 29-APR-07 11.54.10,000000 PM 5 Now I only need ID_ANL, ID_RIC and INDATA with MAX INDATA value, how
can obtain it?
Mariano schrieb: > Then I have this query result: >> select * from analisi_ricovero WHERE id_ric=82
> ID_ANL ID_RIC INDATA VALORE
> 41 82 29-APR-07 11.54.17,000000 PM 43
> 41 82 29-APR-07 11.54.10,000000 PM 5
> Now I only need ID_ANL, ID_RIC and INDATA with MAX INDATA value, how
> can obtain it?
WITH analisi_ricovero AS ( SELECT 41 id_anl, 82 id_ric, to_timestamp('29-APR-07 11.54.17,000000 PM','dd-mon-yy hh:mi:ss,ff AM') indata, 43 valore FROM dual UNION ALL SELECT 41, 82, to_timestamp('29-APR-07 11.54.10,000000 PM','dd-mon-yy hh:mi:ss,ff AM'), 5 FROM dual ) SELECT max(id_anl) KEEP (dense_rank LAST ORDER BY valore), max(id_ric) KEEP (dense_rank LAST ORDER BY valore), max(indata) KEEP (dense_rank LAST ORDER BY valore) FROM analisi_ricovero WHERE id_ric=82 Best regards
Maxim
-----------------------------------------------Reply-----------------------------------------------
On May 5, 10:35 am, Mariano <mariano.calan @gmail.com> wrote: > Then I have this query result: > >select * from analisi_ricovero WHERE id_ric=82
> ID_ANL ID_RIC INDATA VALORE
> 41 82 29-APR-07 11.54.17,000000 PM 43
> 41 82 29-APR-07 11.54.10,000000 PM 5
> Now I only need ID_ANL, ID_RIC and INDATA with MAX INDATA value, how
> can obtain it?
select id_anl, id_ric, max(indata) from analisi_ricovero where id_ric=82 group by id_anl, id_ric -----------------------------------------------Reply-----------------------------------------------
On 5 Mai, 15:35, Mariano <mariano.calan @gmail.com> wrote: > Then I have this query result: > >select * from analisi_ricovero WHERE id_ric=82
> ID_ANL ID_RIC INDATA VALORE
> 41 82 29-APR-07 11.54.17,000000 PM 43
> 41 82 29-APR-07 11.54.10,000000 PM 5
> Now I only need ID_ANL, ID_RIC and INDATA with MAX INDATA value, how
> can obtain it?
You want the one record with max indata time? select id_anl, id_ric, indata, valore
from analisi_ricovero
where indata = (select max(indata) from analisi_ricovero)
Or do you want one record per id_anl and id_ric?
select id_anl, id_ric, indata, valore
from analisi_ricovero
where (id_anl, id_ric, indata) in
(
select id_anl, id_ric, max(indata)
from analisi_ricovero
group by id_anl, id_ric
)
-----------------------------------------------Reply-----------------------------------------------
Analytic function: select ID_ANL,ID_RIC,INDATA from (
select ID_ANL,ID_RIC,INDATA,max(INDATA) over(partition by id_ric)
max_indata, VALORE from analisi_ricovero WHERE id_ric=82
)
where indata = max_indata;
- Siva
|
 |
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
