At 05:10 PM 21/02/2004 +0000, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
>wrote:
> > >SELECT * FROM (SELECT FIRST 10 USERNAME FROM USERS ORDER BY ACCESS
> > >DESC) ORDER BY USERNAME;
> >
> > the Fb 2 development. Actually, your query wouldn't get the *last*
> > 10 users anyway.
>
>No? Why not? I thought the ORDER BY ACCESS DESC would return the
>latest users first, then I pick the first 10 from those... no?

Errrm, no, because I didn't notice you already had DESC there. Order By
Desc is correct.

> > SELECT FIRST 10 * FROM USERS
> > ORDER BY ACCESS DESC
>
>Does the '*' vs 'USERNAME' nake the difference here?

The use of an ORDER BY with SELECT FIRST determines the *rows selected* and
therefore also the output order. If you want to select 10 on the basis of
date, you have to order on the basis of date.

So, for example, if your Order By was ACCESS DESC, USERNAME
you will still get the "top 10" by date but the username ordering would be
within each date, i.e. meaningless.

>Is there another
>way to achieve what I want, maybe something with a temporary table
>(although I couldn't find any info on temp. tables so far).

Yes, in Firebird you "do" temp tables with views or selectable stored
procedures, as appropriate. A view is not possible here because of the
requirement to order (views can't be ordered).

So, with a stored procedure:
SET TERM ^;
create procedure last_10
returns (
full_name varchar(37),
hire_date date)
as
begin

FOR SELECT full_name, hire_date
from employee
order by hire_date desc
into :full_name, :hire_date
do
suspend;
end ^
COMMIT ^
SET TERM ;^

SELECT first 10 full_name, hire_date
from last_10
order by full_name ;

> > A descending index on ACCESS is called for here.
>
>For performance reasons, or in order to achieve the functionality at
>all?

Only for performance reasons. If you don't have a descending index on
ACCESS, the query will have to find the eligible rows itself. Because the
values you want are are the last ones it would find (whether with no index
or with an ascending index) it would have to walk almost the whole table or
index before it found them.

/heLen