This discussion is archived
8 Replies Latest reply: Feb 9, 2013 4:47 PM by 990169 RSS

How to show a unique row for multiple columns

990169 Newbie
Currently Being Moderated
Hello Oracle Gurus,
I am pretty new to SQL development and am hoping someone could help me out

I have an ID that repeats multiple times for 3 different tables based on some condition

For your convenience, here you go

selec a.id,
a.name,
case when b.state=1 then 'K' else 'N' end,
case when c.city=2 then 'K' else 'N' end
from PRIM a
left outer join STATE b on a.id=b.id
left outer join CITY c on a.id=c.id

The results

ID NAME STATE CITY
1 Peter K N
1 Peter N K
2 Stewie K N
2 Stewie N K

An extra row is coming because for Peter ,its Y for State and Y for city.
Expected Results
ID NAME STATE CITY
1 Peter K K
2 Stewie N N


How can I achieve this ? I think I can do a union. but my real query actually has 140 columns . so i am curious to know if there is any other way to approach this?
Is there a way to use distinct?
Thanks in advance.
  • 1. Re: How to show a unique row for multiple columns
    ranit B Expert
    Currently Being Moderated
    Something like this...?
    ranit@XE11GR2>> 
    ranit@XE11GR2>> with xx as( --- "just simulating your input data here"
      2       select 1 id, 'peter' name, 'k' state, 'n' city from dual UNION ALL
      3       select 1 id, 'peter' name, 'n' state, 'k' city from dual UNION ALL
      4       select 2 id, 'stewie' name, 'k' state, 'n' city from dual UNION ALL
      5       select 2 id, 'stewie' name, 'n' state, 'k' city from dual
      6  )
      7  Select DISTINCT
      8  id,
      9  name,
     10  MIN(state) over(partition by id,name order by id) STATE,
     11  MIN(city) over(partition by id,name order by id) CITY
     12  from
     13  xx
     14  where id = 1
     15  UNION
     16  Select DISTINCT
     17  id,
     18  name,
     19  MAX(state) over(partition by id,name order by id) STATE,
     20  MAX(city) over(partition by id,name order by id) CITY
     21  from
     22  xx
     23  where id = 2;
    
            ID NAME   S C
    ---------- ------ - -
             1 peter  k k
             2 stewie n n
    
    Elapsed: 00:00:00.06
    Replace the content content of table 'XX' with the query you have... and check if this suits you.

    HTH
    Ranit B.

    Edited by: ranit B on Feb 10, 2013 4:54 AM
    -- code added
  • 2. Re: How to show a unique row for multiple columns
    990169 Newbie
    Currently Being Moderated
    Thanks,Ranit..But ,just curious.. Is Union the only way to solve this issue?
  • 3. Re: How to show a unique row for multiple columns
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    987166 wrote:
    Hello Oracle Gurus,
    I am pretty new to SQL development
    Welcome to the forum!
    and am hoping someone could help me out
    Sure. Whenever you have a question, please post CREATE TABLE and INSERT statements for your sample data.
    Also post the results you want from that sample data, as you did.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
    I have an ID that repeats multiple times for 3 different tables based on some condition

    For your convenience, here you go

    selec a.id,
    a.name,
    case when b.state=1 then 'K' else 'N' end,
    case when c.city=2 then 'K' else 'N' end
    from PRIM a
    left outer join STATE b on a.id=b.id
    left outer join CITY c on a.id=c.id

    The results

    ID NAME STATE CITY
    1 Peter K N
    1 Peter N K
    2 Stewie K N
    2 Stewie N K
    Are you sure these are the results you get from the query you posted? Obviously, there are 2 rows in the state table with id=1, and 2 rows in the state table with id=1, so the result set should have 2 * 2 = 4 rows with id=1. The smae goes for id=2. This is one reason why you really need to post the sample data.
    An extra row is coming because for Peter ,its Y for State and Y for city.
    What is 'Y"? All the relevant columns in the tables seem to be numbers, and the CASE expressions in the table always return 'K' or 'N', never 'Y'.
    Expected Results
    ID NAME STATE CITY
    1 Peter K K
    2 Stewie N N
    Will the results always have exactly 1 row for every row in the priom table?
    How can I achieve this ?
    It depends on the reasons why you want those results.

    Why do you want these results rather than
    {code}
    ID NAME STATE CITY
    1 Peter K K
    2 Stewie K K
    {code}
    or
    {code}
    ID NAME STATE CITY
    1 Peter N K
    2 Stewie K N
    {code}
    or
    {code}
    ID NAME STATE CITY
    1 Peter N N
    2 Stewie K N
    {code}
    or any other combination?
    I think I can do a union. but my real query actually has 140 columns . so i am curious to know if there is any other way to approach this?
    Again, it depends on your reasons. Describe why you want the results you want.
    You're smart to try to avoid UNION; it's usually slow as well as hard to code. There are many different things I can think of that you might be trying to do, but not of them require UNION.
    Is there a way to use distinct?
    That's one possibility. Another GROUP BY. Yet another is using the analytic ROW_NUMBER function, to guarantee that the result set only has 1 row for every row in prim. Again, I'm not sure that's what you want, and, givcen that it is, I don't know why you want the row you do, and not some other matching row. Please explain what you need to do.
  • 4. Re: How to show a unique row for multiple columns
    chris227 Guru
    Currently Being Moderated
    One usual way to do this is group by.
    So if the structur of your data and query is really like you have shown,you may try first to filter the tables joined to PRIM on the condition in the case-statements.
    So at the end you have just to test wether the column is null or not
    select
     a.id
    ,a.name
    ,decode(max(b.id),null,'N','K')
    ,decode(max(c.id),null,'N','K')
    from PRIM a
    left outer join (select id from STATE where b.state=1) b on a.id=b.id
    left outer join (select id from CITY where c.city = 2 ) c on a.id=c.id
    group by
     a.id
    ,a.name
    Edited by: chris227 on 09.02.2013 16:09
    Completed
  • 5. Re: How to show a unique row for multiple columns
    990169 Newbie
    Currently Being Moderated
    Hi Frank,Thanks for your quick response.Moving forward, I shall definitely follow those guidelines.

    It actually got me to explain it a little easier too :)

    So, I have 2 tables.
    create table Elig_Test (elig_id number,channel varchar(10),partner varchar(10))

    insert into Elig_Test values (915,'Mobile','Peter');

    create table Elig_Suppr(object_id number,part_supp_id number)

    insert into Elig_Suppr values (915,2);

    insert into Elig_Suppr values (915,21);
    insert into Elig_Suppr values (915,7);



    select * from Elig_Test a ,Elig_Suppr b where a.elig_id=b.object_id is the logic behind the join

    This is my SQL;
    select a.elig_id,
    (case when b.part_supp_id=2 then 'Y' else 'N' end) as A,

    (case when b.part_supp_id=21 then 'Y' else 'N' end) as B
    from Elig_Test a ,Elig_Suppr b
    where a.elig_id=b.object_id


    I get 3 rows since I have 3 part_supp_id in the Elig Suppr Table

    Is there a way for me to get


    Elig_ID A B
    915 Y Y


    I want both the conditions to come in the same row.

    Hope that helps. Thanks again
  • 6. Re: How to show a unique row for multiple columns
    990169 Newbie
    Currently Being Moderated
    Oh and my version :Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
  • 7. Re: How to show a unique row for multiple columns
    chris227 Guru
    Currently Being Moderated
    What about
    select
     a.elig_id,
     max(case when b.part_supp_id= 2 then 'Y' else 'N' end) as A,
     max(case when b.part_supp_id=21 then 'Y' else 'N' end) as B
    from
      Elig_Test a
     ,Elig_Suppr b
    where
     a.elig_id=b.object_id
    group by 
    a.elig_id
  • 8. Re: How to show a unique row for multiple columns
    990169 Newbie
    Currently Being Moderated
    Chris,that worked!
    But I am curious ,how the max function worked here.Forgive my ignorace on this subject.
    But thanks a lot.That worked

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points