Re: Getting first and last rank

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 6 Jun 2007 19:36:34 +0200
Message-ID: <4666f0a1$0$6438$426a74cc@news.free.fr>

<puneet.bansal_at_wipro.com> a écrit dans le message de news: 1181150635.593464.7190_at_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 Received on Wed Jun 06 2007 - 12:36:34 CDT