Re: SQL Question.

FWIW the following will work:

 select * from customers
minus
select * from customers
where rownum<(select count(*) -10 from customers) /

regards

Jerry Gitomer

shiling_at_math.wayne.edu wrote in message <7jr81m$hob$1_at_nnrp1.deja.com>...
>The suggestion will not work. Here is the reason,
>
>ROWNUM returns a number indicating the order in which a row was selected
>from a table. The first row selected has a ROWNUM of 1, the second row
>has a ROWNUM of 2, and so on. If a SELECT statement includes an ORDER BY
>clause,ROWNUMs are assigned to the retrieved rows before the sort is
>done.
>
>Usually, a order by clause should come after a where clause.
>
>
>In article <7jqm0o$iak$1_at_lure.pipex.net>,
> "Terry Sullivan" <terry_sullivan_at_sgsgroup.com> wrote:
>> >I wonder if there is a way to select, for instance, the last 10 rows
>of
>> >a table. I know that i can select the first 10 rows by:
>> >
>> >SELECT *
>> >FROM CUSTOMERS
>> >WHERE ROWNUM<10;
>> >
>> >Thanks in advance.
>> >Roberto Pérez Lita.
>>
>> If you use the following code:
>>
>> SELECT *
>> FROM CUSTOMERS
>> ORDER BY CUST_ID
>> WHERE ROWNUM<11;
>>
>> and CUST_ID is a Primary Key then the order in which the rows are
>returned
>> will give you the first 10 rows.
>>
>> Similarly if you use :
>>
>> SELECT *
>> FROM CUSTOMERS
>> ORDER BY CUST_ID DESC
>> WHERE ROWNUM<11;
>>
>> then this would give you the last 10 rows.
>>
>> This is because Oracle assigns ROWNUM in the order in which the rows
>are
>> retrieved. If it uses an index to retrieve the rows (and it should if
>you
>> are using a primary key) then the result should be as expected above.
> I
>> have tried it and it seems to work.
>>
>> Terry
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Fri Jun 11 1999 - 13:25:42 CDT