--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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 t1.id in (select first 10 t2.id from table1 t2 where t2.id <
> >100)
> >order by t1.name
> >
> >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

perception...

>
> 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

I've searched a but further and following while give the desired
result

select p1.PRT_NAME, p1.PRT_ID,
Case
when p1.prt_used > (Select first 1 skip 10 p2.PRT_USED
from BOM_PART p2
order by p2.PRT_USED desc)
then 1
else 0
end

from BOM_PART p1
order by 3 desc, p1.PRT_NAME

Thanks for the reply,

Andy