Hi,

I have to appologize for not being careful enough when posting this
thread.
I just found that FIRST/SKIP works fine. I was sure it is ok and
suspected my query - that's why I posted it here.
The use of View, explicit or implicit JOIN doesn't matter - it works
Ok with all of them.
The problem is that the returned rows are much more than I though.
The join causes duplicated Message ID's (PK), as there may be more
than one entry in the second table (msg_to).
The application layer took care not to display more than once each PK
(Message ID), thus skipping some of the returned rows.

Again, sorry fot misleading...


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

> At 12:39 PM 29/03/2005 +0000, you wrote:
>
>
> >Hi,
> >
> >I found that the problem is in the JOIN.
> >It seems that the FIRST, SKIP is applied before the JOIN is

executed.

>
> FIRST and SKIP are *always* applied to the final output set, so

there's

> another explanation somewhere.
>
> >The second table (msg_to) has more than one record with the same
> >common_id. FB actually applies the FIRST and the SKIP to the

records

> >from the msg_to & messages tables, rather than to the JOINed result
> >set. Then the JOIN is applied and the returned results are
> >from 'messages' after what remained from msg_to.
> >
> >Here is the SQL again, if someone has ideas:
> >
> >select first 30 skip 100 messages.*, msg_to.to_email,

msg_to.to_name,

> >msg_to.to_type from messages,msg_to where (messages.common_id =
> >msg_to.common_id) order by messages.msg_key asc, msg_to.to_type

desc

> >
> >I need to apply the FIRST, SKIP to the result set aftet the JOIN.
> >Any help will be highly appreciated!
>
> Are you certain that the unlimited set would return more than 107

rows? If

> there are not 30 rows left to return, you won't get 30 rows. That

is, the

> initial 100 rows in the set will be skipped before the query begins
> returning rows. It will stop when it has output 30 rows or when it

has run

> out of rows, whichever comes first.
>
> Remember that the inner join is an exclusive join so, potentially,

some

> Messages rows will never get into the unlimited set because of
> no-matches. Also, think of your ordering scheme here and what

happens if

> it actually causes all but 7 records to be skipped.
>
> Your use of the implicit join might be the problem (though I don't

*know*

> of any reason why it should be). Still, try the explicit join.
>
> select first 30 skip 100
> m.*,
> mt.to_email,
> mt.to_name,
> mt.to_type
> from messages m
> join msg_to mt
> on m.common_id = mt.common_id
> order by m.msg_key asc, mt.to_type desc
>
> If it makes a difference, then make a reproducible test case with

your

> output numbers and present a bug report.
>
> ./hb