Re: Help with Psudo Column in SQL Statement

On Aug 29, 9:27 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> 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 --
>
> I can try to apply that. However, if you notice the ORDER BY clause,
> I need the numerical count to be applied AFTER the ORDER BY has been
> processed........
>
> Really messed up, but that is what the client wants.- Hide quoted text -
>
> - Show quoted text -

Which follows the example Mark already presented and I shall reiterate:

SELECT rownum, q.*
from
(SELECT 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) q;

Now do you understand the suggestion?

David Fitzjarrell Received on Wed Aug 29 2007 - 09:33:14 CDT