Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: discarding first and last element from an ordered set of values

Re: discarding first and last element from an ordered set of values

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 20 Feb 2006 15:42:01 +0100
Message-ID: <43f9d5ff$0$13604$9b4e6d93@newsread2.arcor-online.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US