Re: Help with Psudo Column in SQL Statement

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 29 Aug 2007 07:21:23 -0700
Message-ID: <1188397283.488472.252650@k79g2000hse.googlegroups.com>

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 -- Received on Wed Aug 29 2007 - 09:21:23 CDT