Re: sql return only first matching record from join table

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 Wed Jul 03 2002 - 19:30:04 CDT