I would avoid calling any field "Order". That is a reserved SQL word,
and even though it may work, you can bet your bottom dollar that
somewhere along the line, you will use a component or report writer
or something that will refuse to work properly. I am speaking as
someone who had to rename a lot of fields that used to come through
as "desc".

You could quite easily create a stored procedure that would return a
result set with a row number for each row. But unless you
specifically use an order by statement, I wouldn't make any
assumptions about the order the rows are returned to you in. If the
optimiser finds it more convenient, it may use a different index, or
join a different table first etc.


CREATE PROCEDURE "SP_BOOK"
RETURNS
(
ORDERNUM INTEGER,
TITLE VARCHAR(100)
)
AS
DECLARE VARIABLE ICNT INTEGER;
DECLARE VARIABLE VTITLE VARCHAR(100);
BEGIN
ICNT = 1;

FOR SELECT TITLE
FROM BOOK
INTO :VTITLE
DO
BEGIN
ORDERNUM = ICNT;
TITLE = VTITLE;
SUSPEND;
ICNT = ICNT + 1;
END
END
^

Then you could use:

Select *
from SP_BOOK;

I don't see why you would want this though. The Order Number is not
data per se. Your client application may find a number convenient,
but then why not let your client application assign the number and
save some bandwidth and server side processing. The order by part of
your query does not make sense though. You cant say "Please sort this
in the order that you have sorted this".

Adam.




--- In firebird-support@yahoogroups.com, "esrefatak" <liste@y...>
wrote:

>
> --- In firebird-support@yahoogroups.com, "esrefatak" <liste@y...>

wrote:

> >
> > Is there any SQL command that have similiarly function like
> > ROWNUM?, example:
> >
> > "SELECT ROWNUM as ORDER, TITLE FROM BOOK ORDER BY ROWNUM"
> >
> > ---
> > esrefatak
>
> example:
>
> create table book (
> id numeric(10,0),
> title varchar (100),
> author varchar (50)
> );
>
> SELECT ROWNUM as ORDER, TITLE
> FROM BOOK
> ORDER BY ROWNUM;
>
> ORDER TITLE
> ----- -------------------------
> 1 Firebird and security
> 2 ColdFusion and security
> 3 Oracle and speed
> ...
>
>
> ---
> best regards,
> esrefatak