Re: Should be a simple query, please help a desperate person at the end of their pitiful rope.

rownum is done before the order by and hence the behavior you are seeing. You should try to use max(date) - that is pick the more recent one, not sort a set and take the top.)

 SELECT *
 FROM PARKING_SERVICES_ADM.VEHICLE_INFO avi  WHERE RES_PERSON_ID = '8675309'
 AND PERMIT_PURCHASE_DATE =
(select max(PERMIT_PURCHASE_DATE) from
 FROM PARKING_SERVICES_ADM.VEHICLE_INFO avi2 where avi2.RES_PERSON_ID=avi.RES_PERSON_ID and rownum<2 )

Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Ryan McLean" <rdm32_at_dana.ucc.nau.edu> wrote in message
news:dfec50f9.0306051545.3cfb9bbf_at_posting.google.com...


> I am trying to return one of two records.  The most recend one . . .

> why does this not work?

>

> SELECT *

> FROM PARKING_SERVICES_ADM.VEHICLE_INFO

> WHERE RES_PERSON_ID = '8675309'

> AND ROWNUM < 2

> ORDER BY PERMIT_PURCHASE_DATE DESC;

>

> This should be an easy answer . . . thank you in advance for any help.

>

> Thanks,

>

> Ryan

>

> p.s. the result is the other (wrong) record.  This record has an older

> PERMIT_PURCHASE_DATE, I thought the query above would return the

> latest record according to the PERMIT_PURCHASE_DATE field.  If I

> change the rownum < 2 to rownum < 3, I get the correct order of the

> rwo records.

>

> Thanks again!