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.
how to fetch the last inserted record for each patient?
please help me........

<madhusudan.hv@gmail.com> a écrit dans le message de
news:1103707234.740544.285300@z14g2000cwz.googlegr oups.com...
| 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.
| how to fetch the last inserted record for each patient?
| please help me........
|

If you don't already have a timestamp somewhere, you can't.
But if addressuid is an ordered id then you can use it as timestamp.

Regards
Michel Cadot

I am guessing that addressuid is shared by all the patient addresses so
unless there is a column in the address table that has the effect of
ordering the data timewise as Michel suggested then a table change is
required. I would expect that the address table has an entry date
associated with each address. Selecting max(entry_date) from address
where b.addressuid = a.addressuid would work in this case as would a
sequence number value that might have been added to give the table a
PK.

-- Mark D Powell --

Mark D Powell wrote:
> I am guessing that addressuid is shared by all the patient addresses so
> unless there is a column in the address table that has the effect of
> ordering the data timewise as Michel suggested then a table change is
> required. I would expect that the address table has an entry date
> associated with each address. Selecting max(entry_date) from address
> where b.addressuid = a.addressuid would work in this case as would a
> sequence number value that might have been added to give the table a
> PK.
>
> -- Mark D Powell --
>
But, if the addressuid is always increasing (typical sequence) then you
could do the same thing by selecting max(addressuid).

"M Rothwell" <ThisIsABadAddress@toobad.com> wrote in message
news:41c9c800$1@usenet01.boi.hp.com...
> Mark D Powell wrote:
> > I am guessing that addressuid is shared by all the patient addresses so
> > unless there is a column in the address table that has the effect of
> > ordering the data timewise as Michel suggested then a table change is
> > required. I would expect that the address table has an entry date
> > associated with each address. Selecting max(entry_date) from address
> > where b.addressuid = a.addressuid would work in this case as would a
> > sequence number value that might have been added to give the table a
> > PK.
> >
> > -- Mark D Powell --
> >
> But, if the addressuid is always increasing (typical sequence) then you
> could do the same thing by selecting max(addressuid).

No. There is no guarantee that the max sequence number is the last record. A
sequence number is assigned before the row is committed, so if the commit
happens after another sequence number is assigned and committed, a lower
sequence number is the latest record. For example:

SEQ TIME COMMIT_TIME
1 1200 1205
2 1201 1202

The record with sequence #1 is the last record committed. Never use sequence
numbers for ordering purposes. They are usable only as unique values, and
even then only if other conditions are met.

Michel Cadot wrote:

> <madhusudan.hv@gmail.com> a écrit dans le message de
> news:1103707234.740544.285300@z14g2000cwz.googlegr oups.com...
> | 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.
> | how to fetch the last inserted record for each patient?
> | please help me........
> |
>
> If you don't already have a timestamp somewhere, you can't.
> But if addressuid is an ordered id then you can use it as timestamp.
>
> Regards
> Michel Cadot

Unless 10g in which case the following might work:

SELECT ...
FROM ...
WHERE ora_rowscn = (
SELECT MAX(ora_rowscn)
FROM ...);

and will definitely work if the table was built with the ROWDEPENDENCIES
clause.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----