I have a question regarding how to prevent duplicate values in the result of my query.

table 1 has mortgage numbers and pool numbers
table 2 has only pool numbers.
a mortgage number can have multiple pool number so when I do the following entry:

select nbr_mrtg
from table1
inner join table2 on table1.nbr_pool=table2.nbr_pool

I get duplicate mortgage numbers. So how do I get only unique values of mortage number to show up in my query.

Thanks so much in advance

Ben

when you run the query, you will get something like this --

mtge1 pool1
mtge1 pool2
mtge1 pool3
mtge2 pool1
mtge2 pool2

could you please explain what you want by removing duplicates

do you want this (one pool per mortgage) --

mtge1 pool3
mtge2 pool2

or do you want this (suppress mtge number on 2nd and subsequent rows) --

mtge1 pool1
------- pool2
------- pool3
mtge2 pool1
------- pool2

if it's the first, please indicate which pool you want to select for each mortage

if it's the second, do not attempt to do this with sql, do it in your calling program


rudy
http://r937.com/

Thanks for the advice. Since I only want the Mortgage number, I came up with the following solution:

with t1 as ( select a.nbr_mrtg
from udbadm.mrtg_grp_pool_hst a
inner join delete.pre97nbr b
on a.nbr_pool=b.nbr_pool)

Select distinct nbr_mrtg
from t1

simple but works

----------------------------------------------
with t1 as ( select a.nbr_mrtg
from udbadm.mrtg_grp_pool_hst a
inner join delete.pre97nbr b
on a.nbr_pool=b.nbr_pool)

Select distinct nbr_mrtg
from t1
----------------------------------------------
is eq to:

select nbr_mrtg
from table1
inner join table2 on table1.nbr_pool=table2.nbr_pool
group by nbr_mrtg


Abel.

what if I want the one pool per mortgage and want only the smallest pool number?

if all you want is the smallest pool number, that's trivial

select table1.nbr_mrtg
, min(table2.nbr_pool)
from table1
inner
join table2
on table1.nbr_pool=table2.nbr_pool
group by table1.nbr_mrtg

rudy