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

From: John Gilson <jag_at_acm.org>
Date: Tue, 17 Sep 2002 12:14:05 GMT
Message-ID: <h0Fh9.65515$c7.18414009_at_twister.nyc.rr.com>


"Cinderella" <henk.spaan_at_ishbv.nl> 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
>
>
>
>
> 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

For each group, you would like the rows corresponding to the two latest dates, right? Let's say you have the following table (T-SQL)

CREATE TABLE t
(
id INT NOT NULL,
entry_date DATETIME NOT NULL,
PRIMARY KEY (id, entry_date)
)

This query will give you the lastest 2 rows for every id. The HAVING clause condition determines how many of the rows for an id are returned.

SELECT t1.id, t1.entry_date
FROM t AS t1

            INNER JOIN
            t AS t2
            ON t1.id = t2.id AND
                   t2.entry_date >= t1.entry_date
GROUP BY t1.id, t1.entry_date
HAVING COUNT(t2.entry_date) <= 2
ORDER BY t1.id, t1.entry_date

If we execute this with the sample data

INSERT INTO t
VALUES (1, '20020914')
INSERT INTO t
VALUES (1, '20020915')
INSERT INTO t
VALUES (1, '20020916')
INSERT INTO t
VALUES (1, '20020917') INSERT INTO t
VALUES (2, '20020916')
INSERT INTO t
VALUES (2, '20020917') INSERT INTO t
VALUES (3, '20020917') we get

id entry_date

1 2002-09-16
1 2002-09-17
2 2002-09-16
2 2002-09-17
3 2002-09-17

Regards,
jag Received on Tue Sep 17 2002 - 14:14:05 CEST

Original text of this message