ok thanks seam to work (but i don't know how is is internally optimized) ... but seam ok!

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >oops, now i have another probleme
> >
> >Select
> > First 10
> > MAINTABLE.ID
> >from
> > MAINTABLE
> >where
> > (MAINTABLE.name = 'jean')
> >ORDER BY MAINTABLE.DATE
> >
> >UNION
> >
> >Select
> > First 10
> > SECONDTABLE.ID
> >from
> > SECONDTABLE
> >where
> > (SECONDTABLE.name = 'jean')
> >Order by SECONDTABLE.DATE
> >
> >gave me an error :(
>
> What about changing to:
>
> Select
> ID, "DATE"
> from
> MAINTABLE
> where
> (name = 'jean')
> UNION
> Select
> ID, "DATE"
> from
> SECONDTABLE
> where
> (name = 'jean')
> Order by 2
> ROWS 1 TO 10
>
> (assuming you want the first 10 rows rather than the first 10 rows from each table).
>
> If this doesn't work, try the CTE approach:
>
> WITH CTE_MT(ID, MyDate) as
> (Select ID, "DATE"
> from MAINTABLE
> where name = 'jean'
> ORDER BY "DATE"
> Rows 1 TO 10),
> CTE_ST(ID, MyDate) as
> (Select ID, "DATE"
> from SECONDTABLE
> where name = 'jean'
> Order by "DATE"
> Rows 1 TO 10)
>
> SELECT ID
> FROM CTE_MT
> UNION
> SELECT ID
> FROM CTE_ST
> ORDER BY MyDate
> ROWS 1 TO 10
>
> HTH,
> Set
>