Re: Comments on Why???

What this query will do is return one record (gl_acct_no and description) for each distinct gl_acct_no in the table.

Using rowid is just a convenient and fast way to select ONE of several duplicate sets of data (based on the gl_acct_no column).

So, if gene310 has the following data:

GL_ACCT_NO	DESCRIPTION
----------	-----------
17		good account
20		bad account
20		oops I mean really bad account
20		ok ok they are good
25		dad's account
25		dad's FREE account

The result from the query will pick, for each unique GL_ACCT_NO, whatever the minimum ROWID is for the set. This is effectively random, but tends to pick the record inserted first (no guarantees though):

result:

GL_ACCT_NO	DESCRIPTION
----------	-----------
17		good account
20		bad account
25              dad's account

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 370+ Oracle tips, visit:                         <->
<->                                                      <->
<->             www.arikaplan.com                        <->
<->                                                      <->
<->             email: akaplan_at_interaccess.com           <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

 > Good Morning All....
>
> Working on something someone else wrote and maybe I'm missing the obvious.
> But would anyone have any idea as to why someone would use the rowid in this
> query????
>
> insert into temp_big_gllist (glacct, desct)
> select distinct (gl_acct_no), (description)
> from gene310 t1 where t1.rowid =
> (select min(t2.rowid)
> from gene310 t2
> where t1.gl_acct_no = t2.gl_acct_no);
>
> I'm having a hard time understanding why someone would do this and of course
> there are no notes left behind to explain. Anyone else have any ideas????
>
> Thanks In Advance!!
> Take Care,
> Christine Turner
> DBA
> IPS SENDERO
> Scottsdale, Arizona
>
> --
> Author: Christine Turner
> INET: christine.turner_at_ips-sendero.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Fri Jul 28 2000 - 08:33:58 CDT