Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!newsfeed01.sul.t-online.de!newsfeedt0.toon.t-online.de!newsmm00.sul.t-online.de!t-online.de!news.t-online.com!not-for-mail From: Maxim Demenko Newsgroups: comp.databases.oracle.misc Subject: Re: use of ROWNUM Date: Sat, 05 May 2007 15:48:12 +0200 Organization: T-Online Lines: 35 Message-ID: <463C8B1C.8030501@gmail.com> References: <1178372136.972480.217410@h2g2000hsg.googlegroups.com> Reply-To: mdemenko@gmail.com Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Trace: news.t-online.com 1178372894 02 27875 1W7c6ROrFbN-tpq 070505 13:48:14 X-Complaints-To: usenet-abuse@t-online.de To: Mariano X-ID: TFHQUrZGweh7RZPWGL2QM-k3Jl6CRjNPRXfrMb3m8OBgQnJZSnztoR User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) In-Reply-To: <1178372136.972480.217410@h2g2000hsg.googlegroups.com> Xref: news.f.de.plusline.net comp.databases.oracle.misc:79113 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