Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    15

    Unanswered: Nested Table expression

    hello,

    i am firing a query:

    SELECT DBA_NM TITLE,ROW_NUMBER() OVER() ROWNUM
    FROM DW05.FMIS_CLNT_ORGN
    WHERE UPPER(CORP_ID) LIKE RTRIM(UPPER('WPPG00'))||'%'
    AND DBA_NM NOT LIKE 'ZZZ%' order by 1;

    and getting the result:

    TITLE ROWNUM
    ----------------------------------- --------------------
    J. Walter Thompson 1
    Ogilvy & Mather 2
    Ogilvy Public Relations 3

    Next i want to select the row with rownum 1, so i am writing query :


    SELECT TEMP.TITLE
    FROM
    (SELECT DBA_NM TITLE,ROW_NUMBER() OVER() ROWNUM
    FROM DW05.FMIS_CLNT_ORGN
    WHERE UPPER(CORP_ID) LIKE RTRIM(UPPER('WPPG00'))||'%'
    AND DBA_NM NOT LIKE 'ZZZ%' order by 1
    ) AS TEMP
    WHERE TEMP.ROWNUM=1;

    and result is:

    TITLE
    -----------------------------------
    Ogilvy & Mather

    But i am expectig to get J. Walter Thompson as a result.

    can any one explain why it is like this and how do i get the expected result?

    thanks

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    SELECT TEMP.TITLE
    FROM
    (SELECT DBA_NM TITLE,ROW_NUMBER() OVER(order by DBA_NM) ROWNUM
    FROM DW05.FMIS_CLNT_ORGN
    WHERE UPPER(CORP_ID) LIKE RTRIM(UPPER('WPPG00'))||'%'
    AND DBA_NM NOT LIKE 'ZZZ%'
    ) AS TEMP
    WHERE TEMP.ROWNUM=1;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •