Re: SQL Question.

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 - 09:55:55 CDT