Re: Getting first and last rank

Thank you very much Michael. I should have got this one!

Puneet

On Jun 6, 12:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <puneet.ban..._at_wipro.com> a écrit dans le message de news: 1181150635.593464.7..._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:41:36 CDT