Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: discarding first and last element from an ordered set of values
Cris Carampa schrieb:
> I need to discard the first and last element from an ordered set of
> values returned by a query.
>
> For example, I have the following query:
>
> select
> data
> from
> tab
> order by
> data
> ;
>
> The result (6 rows) is:
>
> 260
> 260
> 266
> 270
> 273
> 274
>
> But I need to discard the first and last element, so I need only 4 values:
>
> 260
> 266
> 270
> 273
>
> I found out that I can easily discard the first element with the
> following query:
>
> select
> data
> from
> (
> select
> data,
> row_number() over (order by data) rwnum
> from
> tab
> )
> where
> rwnum>1
> ;
>
> But how can I discard the last element, giving that I don't know in
> advance how many rows will be returned by the query?
>
> Thank you. Kind regards,
>
Slightly modified your query:
SELECT DATA FROM (
SELECT DATA ,
row_number() over(ORDER BY DATA) rwnum,
COUNT(*) over() cnt
FROM tab)
WHERE rwnum>1 AND rwnum<>cnt
Best regards
Maxim Received on Mon Feb 20 2006 - 08:42:01 CST