Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.ision.net!newsfeed2.easynews.net!ision!newsfeed.arcor.de!89.84.3.121.MISMATCH!club-internet.fr!feedme-small.clubint.net!feeder1-1.proxad.net!proxad.net!feeder2-2.proxad.net!cleanfeed4-a.proxad.net!nnrp9-1.free.fr!not-for-mail From: "Michel Cadot" Newsgroups: comp.databases.oracle.server References: <1181150635.593464.7190@j4g2000prf.googlegroups.com> Subject: Re: Getting first and last rank Date: Wed, 6 Jun 2007 19:36:34 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Lines: 35 Message-ID: <4666f0a1$0$6438$426a74cc@news.free.fr> Organization: Guest of ProXad - France NNTP-Posting-Date: 06 Jun 2007 19:36:33 MEST NNTP-Posting-Host: 82.67.171.166 X-Trace: 1181151393 news-3.free.fr 6438 82.67.171.166:1479 X-Complaints-To: abuse@proxad.net Xref: news.f.de.plusline.net comp.databases.oracle.server:199034 a écrit dans le message de news: 1181150635.593464.7190@j4g2000prf.googlegroups.com... | | Using Oracle 9.2.0.4 | | | I have a requirement to get the first and last rows when the data is | ordered by time in a table. The only way I have been able to do is to | use rank() twice (first two sort ascending and then sort descending), | obtain two result sets, union them and then filter on rank = 1. This | works but I don't like the idea of having to sort twice. Can I do this | in a single query without having to sort it twice and union it. | | Thanks. | Puneet | SQL> select ename, sal 2 from (select ename, sal, 3 rank() over (order by sal) rk1, 4 rank() over (order by sal desc) rk2 5 from emp) 6 where rk1=1 or rk2=1 7 / ENAME SAL ---------- ---------- KING 5000 SMITH 800 2 rows selected. Regards Michel Cadot