Re: query issue

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 30 Jan 2004 06:45:27 -0800
Message-ID: <2687bb95.0401300645.7acda381_at_posting.google.com>

josephcurwen_at_despammed.com (curwen) wrote in message news:<cc68edc4.0401290135.42671458_at_posting.google.com>...
> > JC, there is no guarantee that the order that the rows display in is
> > the same as the insert order since Oracle is going to look for and
> > insert into blocks with free space in them. Guido, pointed you at the
> > solution: you have to use the max progressive id for a numb fk1 value
> > combination to find the last inserted value pair.
> >
> > HTH -- Mark D Powell --
>
> ok, do you mean something like this:
>
> select * from numbers where n_id in
> (select max(n_id) from numbers group by n_number);
>
>
> in your opinion is there any way to avoid the nested query?
> jc

Observe:
SQL> select * from marktest2;

     TCOL1 TCOL2
---------- ----------

   1231456          1
   1231456          2
   1231456          3
   1231456          4
   3211456          1
   3211456          3
   3211456          2  <= notice value 2 stored physically after later

insert

7 rows selected.

  1 select * from marktest2 a
  2 where tcol2 = ( select max(tcol2) from marktest2 b

  3*                 where  b.tcol1 = a.tcol1 )

SQL> /      TCOL1 TCOL2
---------- ----------

   1231456          4
   3211456          3

The above will work well if you enter the outer query using tcol1, but if you want a solution without a sub-query then convert the subquery into a join:

  1 select a.*
  2 from marktest2 a

  3     ,(select b.tcol1, max(b.tcol2) as tcol2
  4         from   marktest2 b
  5         group by b.tcol1
  6       ) c

  7 where a.tcol1 = c.tcol1
  8* and a.tcol2 = c.tcol2
SQL> /      TCOL1 TCOL2
---------- ----------

   1231456          4
   3211456          3

However, if you want all the max id's for a value then just run the group by query and no outer query or join is necessary at all.

HTH -- Mark D Powell -- Received on Fri Jan 30 2004 - 15:45:27 CET