Re: how to form a sql-statement that returns the last two entered records per group

henk.spaan_at_ishbv.nl (Cinderella) wrote in message news:<7499b35d.0209170153.383edeae_at_posting.google.com>...
> thank you for your kind reply, lennart,
> but it's not exactly what i need.
> that's due to some inaccuratenesses on my part.
> i wrote 'last entered' where i meant having max date and the date
> previous to max date
> furthermore : the database is an access database and i cannot use
> 'partition'
> or 'rownumber'
> do you still consider it possible to form a query that would do what i
> need.
>
> thanks
>

Hmmm, I'm in a bit of a rush for the moment but here is something you can try (I dont have access to access so I can not try it). I'll also give a brief description of the idea, perhaps you can do something out of that in case it doesnt work. Second, I'm not sure I understand your remark about 'last entered' where i meant having max date. Could you elaborate on why this might be a problem?

select

    p1.personid, p1.groupid
from

    persons p1,

           (select groupid, max(creation) from persons group by groupid

            union 
            select groupid, max(creation) from persons p2 where 
            creation < (select max(creation) from persons where 
                        groupid = p2.groupid
                       ) 
            group by groupid) as x(groupid, creation) 

    p1.groupid = x.groupid and
    p1.creation = x.creation

The query uses an expression as a table x(groupid, creation). This table is constructed as:  

    groupid and creation for the max creation in each group     union
    groupid and creation for the second max creation in each group

The second part is constructed by stating that the creation must be less than the max creation in each group. Note that you will get more than the last 2 in case there are 2 persons created at the same time.

Finally the persons table is joined with x inorder to get the personid's

HTH
/Lennart Received on Tue Sep 17 2002 - 20:24:42 CEST