[firebird-support] Re: how to optimize this query ?

[Svein Erling Tysvær]

>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