Thanks Martin. I had done something similar and posted it before yours
showed up here (actually I think I'll modify what I've done based on
your example, should speed things up). The only other thing that I
needed to do was that I had to put the rowid statement in the from
clause because I needed an outerjoin on the rowid statement (which is
not permitted), so this seemed to be a viable workaround.
Martin Doherty <martin.doherty_at_oracle.com> wrote in message news:<3D239ABC.5241A5D_at_oracle.com>...
> (* screwed up my table aliases, resending with correct SQL I hope this
> time *)
>
> Not the most efficient approach, but it might help to get the creative
> juices flowing, so here goes with a correlated subquery:
>
> select a.address, o1.occupant
> from address_table a, occupant_table o1
> where a.address_id = o1.address_id
> and o1.rowid = (select min(rowid)
> from occupant_table o2
> where o2.address_id = a.address_id)
>
> Should be OK performance if you're not dealing with a large number of
> rows. You could also use occupant_id instead of rowid as long as it's
> unique.
>
> Martin
>
> Barnoit wrote:
>
> > Sorry, I guess I should have been more specific in my post. I realize
> > that a relational table is unordered, I simply meant the first row
> > returned, I'm not meaning the first record stored. But more
> > importantly, when I simplified my example, I guess I failed to mention
> >
> > that this query can and most often will return more than one row, so
> > using rownum = 1 will not work. It is only on that specific join
> > column that I used in the example where I want to return only one
> > record. I realize it might seem odd, but that is the nature of the
> > datamodel I have been supplied (and cannot alter). So a query might
> > return more than one row, but I only want it to give me one record
> > when it does the join, even though more than one record may satisfy
> > the where clause. Here is the real scenario, no simplification. I have
> >
> > a query that receives user input, which is a parcel (or parcel id -
> > like a city block). On that parcel there may be 1 or more addresses.
> > At each address there may be one or more occupants. I won't get into
> > the details of the tables, but there are numerous joins. What I want
> > then is when the user provides a parcel to query on, I want it to
> > return me all the distinct addresses for that parcel with only one
> > occupant (I don't care which one) per address. Sorry to be so long
> > winded, but hopefully this should avoid confusion.
> >
> > Thanks in advance for any help.
> >
> > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> > news:<IBDU8.26883$Hj3.82905_at_newsfeeds.bigpond.com>...
> > > Hi Barnoit,
> > >
> > > Simply add 'and rownum=1' to the where condition. Oracle will then
> only
> > > return the first row that meets the other conditions.
> > >
> > > Good Luck
> > >
> > > Richard
> > > "Barnoit" <jbarney_ca_at_yahoo.ca> wrote in message
> > > news:cd2c60c8.0207030600.39d024b8_at_posting.google.com...
> > > > Just to simplify my example, I have 2 tables that are joined on a
> key
> > > > id (there are actually quite a few joins, but this is the one
> causing
> > > > me trouble). The keyid on the first table is unique, but there is
> a
> > > > one to many relationship in the second table (it is actually a
> street
> > > > address table and an occupant table). What I need to be able to do
> is
> > > > to a select using the keyid as the join criteria, but I only want
> to
> > > > return the first matching record and disregard any other matches.
> So
> > > > just to reconfirm, I want to do a select based on an address, join
>
> > > > based on the keyid and return the name of the first person that it
>
> > > > finds, disregarding any other occupants it finds. Any suggestions
> > > > would be greatly appreciated.
> > > > ex.
> > > > select table1.address, table2.name
> > > > from table1, table2
> > > > where address = '123 Main St'
> > > > and table1.keyid = table2.keyid
> > > >
> > > > What I am getting is
> > > > 123 Main St, Joe Blow
> > > > 123 Main St, John Q. Public ...
> > > >
> > > > but what I really want is whatever the first match is. I can't use
>
> > > > pl/sql in this case, has to be sql only. I'm hoping I'm just
> > > > overlooking something very simple, and BTW this is on oracle 8.0.5
>
> --
Received on Thu Jul 04 2002 - 08:56:14 CDT
