I have a this SQL Statement

select DECODE ((SELECT MI.IND

from MAMA MI

where MI.MBD =147193

and MI.IND=182 and

TRUNC(SYSDATE) between MI.FROM_DATE and MI.TO_DATE and

ROWNUM<2 order by mi.CREATED_DATE asc

),'Y','CRIMINAL','N','NOT CRIMINAL' ) PEOPLE from DUAL





Anytime i take the "order by mi.CREATED_DATE asc" out. It executes well
(But it does not serve my purpose). What i am trying to do is i have a table
like

IND MBD CREATED_DATE

Y 147193 02/05/2006

N 147193 02/06/2006

N 147194

Y 147194



I am trying to get the IND status of a person based on the most recent
"created Date". The decodes executes and returns CrminaNot Criminal

Hi!
As you see: take care about rownum, sometimes you don't get what you might
expect to get.
Use analytic functions instead.
/Bjoern

"voodoo" <oyesiji (AT) hotmail (DOT) com> skrev i en meddelelse
news:R9qMf.1939$DT.370 (AT) trnddc06 (DOT) ..