Subject Re: Problem with select statement using FIRST
Author woelyc
I have a dataset where there is a counter from usage. I want to get
the 10 most used records first and then the rest of the dataset. So
my select statement would be like this :

, p1.PRT_ID
, Case
when p1.prt_id in
(Select first 10 p2.PRT_ID
from BOM_PART p2
order by p2.PRT_USED desc)
then 1
else 0
from BOM_PART p1
order by 3, p1.PRT_NAME

In this exmaple is PRT_USED the counter that shows how many times
the record is used. PRT_NAME is the name of a part and PRT_ID is the
primary key.
When I execute this statement then the third field gives for all the
records value 1.

It is the same problem as in the first example. The sub-select while
give not the first 10 records but while give the whole dataset


--- In, Helen Borrie <helebor@t...>
> At 12:35 PM 27/09/2003 +0000, you wrote:
> >Hi,
> >
> >I'm having a problem with the following select statement.
> >
> >select t1.*
> >from table1 t1
> >where in (select first 10 from table1 t2 where <
> >100)
> >order by
> >
> >This example will give all the records of the dataset but I only
> >want 10 records of the dataset.
> >
> >Is there a problem with the select statement or is it a bug in
> >Firebird?
> Perhaps a combination of scrambled cursors and scrambled
> How is the output from this plain-jane query spec different to
what you are
> seeking to get:
> select first 10 * from table1
> where id < 100
> order by name
> heLen