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

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;