8 Replies Latest reply: Feb 9, 2013 6:47 PM by 990169 RSS

    How to show a unique row for multiple columns

    990169
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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