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

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

lennart_at_kommunicera.umea.se (Lennart Jonsson) wrote in message news:<6dae7e65.0209142216.36335ee7_at_posting.google.com>...
> henk.spaan_at_ishbv.nl (Cinderella) wrote in message news:<7499b35d.0209141330.38db2290_at_posting.google.com>...
> > i've got a lot of groups in my database.
> > for most -but not all- of these groups i enter a row in a groupdetail record daily.
> > now i want to form a sql-statement that returns
> > the last two entered rows for each group.
> > i've not been able to do that in one sql-statement.
> > can anyone help me with this?
> >
> > much appreciated.
>
> Dont know if your db supports it, but if it does you may try something
> like:
>
> -- first some ddl for the table
> create table persons (personid integer not null primary key, groupid
> integer not null, creation timestamp)
>
> -- then some data
> insert into persons values (1,1,current timestamp), (2,1,current
> timestamp-1 hour),(3,1,current timestamp-2 hour)
> insert into persons values (11,11,current timestamp), (12,11,current
> timestamp-1 hour),(13,11,current timestamp-2 hour)
> insert into persons values (111,111,current timestamp),
> (112,111,current timestamp-1 hour),(113,111,current timestamp-2 hour)
>
> -- and now the quesry
> select personid, groupid, rn from (select personid, groupid ,
> rownumber() over (partition by groupid order by creation desc) as rn
> from persons) as x where rn <= 2"
>
> PERSONID GROUPID RN
> ----------- ----------- --------------------
> 1 1 1
> 2 1 2
> 11 11 1
> 12 11 2
> 111 111 1
> 112 111 2
>
> 6 record(s) selected.
>
> HTH
> /Lennart
Received on Tue Sep 17 2002 - 11:53:48 CEST