Re: sql query`

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 22 Dec 2004 11:10:18 +0000 (UTC)
Message-ID: <cqbkmq$j76$1@hercules.btinternet.com>

The generic answer to your question is that you can't identify 'the most recent record inserted'.

However, guessing from your table structure and your more detailed comment you may have a possible option.

ASSUMING that "addressuid" is a meaningless unique id for addresses that is monotonic increasing (i.e. a new address always has a higher number than a previous address - in particular you have to GUARANTEE that the newer of two addresses for a patientuid will definitely have a higher addressuid than the older), then the following strategy will work - but won't always perform well.

select

    *
from patient_addresses pa1
where pa1.patientuid = {your input}
and pa1.addressuid = (

        select max(pa2.addressuid)
        from    patient_addresses pa2
        where    pa2.patientuid = pa1.patientuid
        -- and pa2.patientuid = {your input}

    )

NOTE - I have included the second (commented) predicate on the subquery because some versions of Oracle will do a better optimisation of the query if it is there. Technically it ought to be redundant.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





<madhusudan.hv_at_gmail.com> wrote in message 
news:1103706533.567166.236550_at_z14g2000cwz.googlegroups.com...


> thanks for replying.

> my table structure is like this

> tablename: address

>

> 1.patientuid - foreign key referring to patient table

> 2.addressuid

> 3. street

> 4. city

> 5. addresstype - foreign key referring to address table

>

> this database is currently running on many client sites.

> the application allows us to insert multiple addresses and it used

> to show all of them on the gui.

> now according to the new requirement we have show only the last

> inserted

> address by making changes only in query code and not touching the

> database.

> so please help me........

> 

Received on Wed Dec 22 2004 - 05:10:18 CST