Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with Psudo Column in SQL Statement

Re: Help with Psudo Column in SQL Statement

From: <amerar_at_iwc.net>
Date: Wed, 29 Aug 2007 07:31:53 -0700
Message-ID: <1188397913.275108.27220@r29g2000hsg.googlegroups.com>


On Aug 29, 9:21 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Aug 29, 9:52 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
>
>
> > Hi All,
>
> > I have a query in which I need the first column to basically be the
> > row number. This is not the ROWNUM psudo column, just simply a
> > counter. The query is below. I basically need the number of the row
> > in the result set.......
>
> > Any ideas how I can implement this into the current query???
>
> > Thanks for your help.
>
> > SELECT NULL, m.comp_name, m.ticker, NVL(sd.avg_rating,-9999),
> > NVL(ROUND(sd.avg_rating_pr,2),-9999),
> > NVL(sd.num_up_rating_1w,-9999),
> > NVL(sd.num_dn_rating_1w,-9999)
> > FROM master_table m, stock_data sd
> > WHERE m.m_ticker=sd.m_ticker(+) AND avg_rating IS NOT NULL AND
> > m.m_ticker IN
> > (SELECT m_ticker FROM comp_ind WHERE ind_code = UPPER('197'))
> > ORDER BY sd.avg_rating, m.comp_name;
>
> > So:
>
> > 1 ...data
> > 2 ...data
> > 3 ...data
> > etc......
>
> The rownum psuedo column is just a counter whose value is tried only
> to the order in which rows are returned in that specific execution of
> the query and has no actual physical mapping to the rows.
>
> This means you get results like this:
>
> UT1 > select rownum, ename from emp order by hiredate;
>
> ROWNUM ENAME
> ---------- ----------
> 1 SMITH
> 2 ALLEN
> 3 WARD
> 4 JONES
> 6 BLAKE
> 7 CLARK
> 10 TURNER
> 5 MARTIN
>
> because the count was applied before the sort
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=16 Bytes=224)
> 1 0 SORT (ORDER BY) (Cost=4 Card=16 Bytes=224)
> 2 1 COUNT
> 3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=16 Bytes=224
> )
>
> However, will this work?
>
> select rownum, a.*
> from (select ename from emp order by hiredate) a
> ROWNUM ENAME
> ---------- ----------
> 1 SMITH
> 2 ALLEN
> 3 WARD
> 4 JONES
> 5 BLAKE
> 6 CLARK
> <snip>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=16 Bytes=112)
> 1 0 COUNT
> 2 1 VIEW (Cost=4 Card=16 Bytes=112)
> 3 2 SORT (ORDER BY) (Cost=4 Card=16 Bytes=224)
> 4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=16 Bytes=2
> 24)
>
> There is also an analytic function row_number that may be of interest.
>
> HTH -- Mark D Powell --

Mark,

What you suggested seems to work. Thanks a bunch for your efforts. Received on Wed Aug 29 2007 - 09:31:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US