Nick,

SQL> /* setup ... */
SQL> create table T1(C1 char(3));
SQL> commit;
SQL> insert into T1 values ('AAA');
SQL> insert into T1 values ('BBB');
SQL> insert into T1 values ('CCC');
SQL> insert into T1 values ('DDD');
SQL> insert into T1 values ('EEE');
SQL> commit;
SQL>
SQL> /* Output will have the record count in the first row with (sorted) data in the following rows */
SQL> /* For more info see the FB 2.0 Release Notes on Derived Tables ... */
SQL>
SQL> select 2 RecType, 0 RecCount, T2.C2 from (select first 3 skip 1 T1.C1 C2 from T1 order by 1 desc) as T2
CON> union
CON> select 1 RecType, (select count(T3.C3) from (select first 3 skip 1 T1.C1 C3 from T1) as T3) RecCount, 'xxx' Dummy from RDB$DATABASE
CON> order by 1 asc, 3 desc;

RECTYPE RECCOUNT C2
============ ============ ======
1 3 xxx
2 0 DDD
2 0 CCC
2 0 BBB
SQL>
HTH,
John

> -------Original Message-------
> From: Nick Upson <nick.upson@...>
> Subject: Re: [firebird-support] select first & union
> Sent: 23 Aug '07 23:50
>
> It must be my ignorance but I can't see how that would help, could you
> show an example or details
>
> On 23/08/07, jft <[LINK: mailto:jft%40worrigee.net] jft@...>
> wrote:
> > Then FB 2.0's Derived Tables feature (instead of a stored procedure)
> may help ...
> > John
> > > -------Original Message-------
> > > From: Nick Upson <[LINK: mailto:nick.upson%40gmail.com]
> nick.upson@...>
> > > Subject: Re: [firebird-support] select first & union
> > > Sent: 23 Aug '07 20:27
> > >
> > > I can go that route but I'd prefer not to as there are a lot ot
> > > possible params in the where clause that I'd have to pass in and have
> > > lots of
> > >
> > > where (column1 = :param1 or param1 is null)
> > >
> > > On 23/08/07, sasha <[LINK: mailto:selectnull%40gmail.com]
> > > [LINK: mailto:selectnull%40gmail.com] selectnull@...> wrote:
> > > > >
> > > > > almost, the problem is that the select first has no real
> > > > > meaning without an order by on that select within the union,
> > > > > but an order by at the point is not allowed
> > > > >
> > > > > ideally I would do:
> > > > >
> > > > > select first 25 skip 25 'A', id, result from ... ORDER BY id
> > > > > union select 2, count(*) from ...
> > > > > order by 1, 2
> > > > >
> > > > > but I'm not allowed to do the "ORDER BY id" because it's
> > > > > within the union
> > > > >
> > > >
> > > > I guess in that case selectable stored procedure should be able to
> > > produce
> > > > the set that you want.
> > > >
> > > > Sasha
> > > >
> > > >
> > > >
> > > >
> > >
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > > >
> > > > Visit [LINK: [LINK: http://www.firebirdsql.org]
> http://www.firebirdsql.org] [LINK: http://www.firebirdsql.org]
> http://www.firebirdsql.org and
> > > click the Resources item
> > > > on the main (top) menu. Try Knowledgebase and FAQ links !
> > > >
> > > > Also search the knowledgebases at [LINK: [LINK:
> http://www.ibphoenix.com] http://www.ibphoenix.com]
> > > [LINK: http://www.ibphoenix.com] http://www.ibphoenix.com
> > > >
> > > >
> > >
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > > >
> > > > Yahoo! Groups Links
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
> >
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit [LINK: http://www.firebirdsql.org] http://www.firebirdsql.org and
> click the Resources item
> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at [LINK: http://www.ibphoenix.com]
> http://www.ibphoenix.com
> >
> >
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
>