Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    10

    Unanswered: Help with getting rid of duplicate values

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  3. #3
    Join Date
    Sep 2003
    Posts
    10
    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

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    ----------------------------------------------
    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.

  5. #5
    Join Date
    Sep 2003
    Posts
    10
    what if I want the one pool per mortgage and want only the smallest pool number?


    Originally posted by r937
    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/

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •