Sean,

> No, it won't the FIRST 4 syntax will restrict the number of
> details that the query will return.
>

I guess it is easier to explain in sql than in english, so let me try that
way.

create table master(id integer);
create table detail(id integer, dt date);
commit;
insert into master(id) values(1);
insert into detail(id, dt) values (1, '2007-09-01');
insert into detail(id, dt) values (1, '2007-09-02');
insert into detail(id, dt) values (1, '2007-09-03');
insert into detail(id, dt) values (1, '2007-09-04');
insert into detail(id, dt) values (1, '2007-09-05');
commit;

This is your original query, with changed order by column (you posted "order
by d2.date", i changed column name to "dt")

select d.* from detail d
join master m on d.id = m.id
where d.id in (
select first 4 d2.id from detail d2
where d2.id = m.id
order by d2.dt desc)

It returns ALL five rows. Why? Because it is equivalent to this:

select d.* from detail d
join master m on d.id = m.id
where d.id in (1, 1, 1, 1)

This is a fix to return only 4 rows per master row:

select d.* from detail d
join master m on d.id = m.id
where /**/d.dt/**/ in (
select first 4 /**/d2.dt/**/ from detail d2
where d2.id = m.id
order by d2.dt desc)

I've marked my changes with /**/ pairs so it's noticable.

> > > Did you try it?
> > >
> > Yes I did. Did you? :)
>
> Yes!!!
>

Since we both tried it, I guess it was a typo. :)

> I created an equivalent database which I have millions of rows.
>

5 are enough :) and it's hard to spot error among millions of rows. But
serioiusly, did you run a query on million rows? How long did it take? Can
you post results? I guess this kind of query would, as you've said perform
as wounded dog, but I haven't tested it and I'm rather curious.

Sasha