Re: SQL query

From: Ezr Vinh <d_a_p_at_my-deja.com>
Date: Thu, 18 Jan 2001 19:36:35 GMT
Message-ID: <947gk2$n14$1@nnrp1.deja.com>

Well, you can't use rownum with an order by, because the rownum is returned according to the natural order BEFORE the order by is applied. I don't think it can be done with a simple SQL statement.

You can do this with a cursor loop in a PL/SQL block though:

set serveroutput on size 1000000;
declare

cursor c1 is
select color from color_table order by color; begin

for r1 in c1 loop

dbms_output.put_line(c1%rowcount||' '||r1.color); end loop;
end;
/

In article <947dm7$je9$1_at_newsg2.svr.pol.co.uk>, "Happy" <allan_at_livvy80.freeserve.co.uk> wrote:
> Is it possible in SQL to display the contents of a table in a
particular
> order, along with
> a numeric column that shows the sequence of each row?
>
> For example, a table with one column, color, has the following 3
values:
>
> Green
> Blue
> Red
>
> Say I wanted the alphabetic listing along with the numeric sequence,
>
> 1 Blue
> 2 Green
> 3 Red
>
> Can it be done?
>
> Cheers
> Allan
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 18 2001 - 13:36:35 CST