Re: last 3 in group

A copy of this was sent to smiths9312_at_aol.com (if that email address didn't require changing) On Thu, 10 Jun 1999 06:02:57 -0800, you wrote:

>Here's my problem. I need the last 3 by date items in each group. See
>example below:
>A 10 1/1/99
>A 20 1/21/99
>A 30 2/1/99
>A 45 2/23/99

>B 10 1/1/99
>B 15 1/11/99
>B 20 2/11/99
>B 25 2/28/99
>Query would return:
>A 20 1/21/99
>A 30 2/1/99
>A 45 2/23/99

>B 15 1/11/99
>B 20 2/11/99
>B 25 2/28/99
>
>I have run into a brick wall with this one. Any help you can provide would
>be appreciated.
>Thanks!
>
>Steve Smith
>
>
>
> -**** Posted from RemarQ, http://www.remarq.com/?a ****-
> Search and Read Usenet Discussions in your Browser - FREE -

One way is:

SQL> create table t ( cd char(1), cnt int, dt date ); Table created.

SQL> insert into t values ( 'A',         10, to_date('1/1/99','mm/dd/yy'));
SQL> insert into t values ( 'A',         20, to_date('1/21/99','mm/dd/yy'));
SQL> insert into t values ( 'A',         30, to_date('2/1/99','mm/dd/yy'));
SQL> insert into t values ( 'A',         45, to_date('2/23/99','mm/dd/yy'));
SQL> insert into t values ( 'B',         10, to_date('1/1/99','mm/dd/yy'));
SQL> insert into t values ( 'B',         15, to_date('1/11/99','mm/dd/yy'));
SQL> insert into t values ( 'B',         20, to_date('2/11/99','mm/dd/yy'));
SQL> insert into t values ( 'B',         25, to_date('2/28/99','mm/dd/yy'));

SQL> select cd, cnt, to_char(dt,'mm/dd/yy') dt   2 from t t1
  3 where 3 >= ( select count(*)

  4                          from t t2
  5                         where t2.cd = t1.cd
  6                           and t2.dt >= t1.dt )

C CNT DT
- ---------- --------

A         20 01/21/99
A         30 02/01/99
A         45 02/23/99

B         15 01/11/99
B         20 02/11/99
B         25 02/28/99

6 rows selected.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation