Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql query`

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US