1 2 Previous Next 21 Replies Latest reply: Sep 14, 2007 7:35 AM by 597990 Go to original post RSS
      • 15. Re: Select only records with same Id
        450441
        Sorry Dave, I'm not sure to understand your
        question.
        In your query as written, if COUNT(*) was replaced by COUNT(DISTINCT NAME), wouldn't that solve the problem of 2 "Acer" records but no "Fagus" ? i.e. it would return 1 in that instance so would not be included. It would only be > 1 if both Acer and Fagus were present.

        select id,name,region   
         from (select id,name,region,   
              count(distinct name) over(partition by id) cnt   
              from mydatenview)   
          where name in ('Acer','Fagus') 
          and cnt > 1;
        Message was edited by:
        Dave Hemming - fix formatting
        • 16. Re: Select only records with same Id
          Nicolas.Gasparotto
          No, just in case you have 'Acer' with an other value (but not 'Fagus') , your query will return the row, where mine not, as requested.
          SQL> create table mydatenview as
            2  select 1 id, 'Acer' name, 3 region from dual union all
            3  select 1 id, 'Betula' name, 1 region from dual union all
            4  select 1 id, 'Corylus' name, 2 region from dual union all
            5  select 1 id, 'Fagus' name, 1 region from dual union all
            6  select 1 id, 'Fagus' name, 2 region from dual union all
            7  select 1 id, 'Fagus' name, 3 region from dual union all
            8  select 2 id, 'Alnus viridis' name, 1 region from dual union all
            9  select 2 id, 'Alnus viridis' name, 2 region from dual union all
          10  select 3 id, 'Corylus' name, 1 region from dual union all
          11 select 4 id, 'Acer' name, 7 region from dual union all
          12 select 4 id, 'Other' name, 8 region from dual union all

          13  select 5 id, 'Fagus' name, 4 region from dual union all
          14  select 6 id, 'Acer' name, 6 region from dual union all
          15  select 6 id, 'Fagus' name, 9 region from dual union all
          16  select 7 id, 'Fagus' name, 8 region from dual union all
          17  select 8 id, 'Acer' name, 2 region from dual ;

          Table created.

          SQL> select id,name,region  
            2   from (select id,name,region,  
            3        count(distinct name) over(partition by id) cnt  
            4        from mydatenview)  
            5    where name in ('Acer','Fagus')
            6    and cnt > 1;

                  ID NAME              REGION
          ---------- ------------- ----------
                   1 Acer                   3
                   1 Fagus                  1
                   1 Fagus                  2
                   1 Fagus                  3
          4 Acer 7
                   6 Acer                   6
                   6 Fagus                  9

          7 rows selected.

          SQL> select id, name, region
            2  from   (select id, name, region,
            3                 count(distinct name) over (partition by id ) ct
            4          from   mydatenview
            5          where  name in ('Acer','Fagus'))
            6  where   ct > 1;

                  ID NAME              REGION
          ---------- ------------- ----------
                   1 Acer                   3
                   1 Fagus                  1
                   1 Fagus                  2
                   1 Fagus                  3
                   6 Acer                   6
                   6 Fagus                  9

          6 rows selected.
          Nicolas.
          • 17. Re: Select only records with same Id
            Aketi Jyuuzou
            This thread deals similar question.
            Newbie: Simple select with 'IN ALL'
            with mydatenview as(
            select 1 id, 'Acer' name, 3 region from dual union all
            select 1 id, 'Betula' name, 1 region from dual union all
            select 1 id, 'Corylus' name, 2 region from dual union all
            select 1 id, 'Fagus' name, 1 region from dual union all
            select 1 id, 'Fagus' name, 2 region from dual union all
            select 1 id, 'Fagus' name, 3 region from dual union all
            select 2 id, 'Alnus viridis' name, 1 region from dual union all
            select 2 id, 'Alnus viridis' name, 2 region from dual union all
            select 3 id, 'Corylus' name, 1 region from dual union all
            select 4 id, 'Acer' name, 7 region from dual union all
            select 4 id, 'Other' name, 8 region from dual union all
            select 5 id, 'Fagus' name, 4 region from dual union all
            select 6 id, 'Acer' name, 6 region from dual union all
            select 6 id, 'Fagus' name, 9 region from dual union all
            select 7 id, 'Fagus' name, 8 region from dual union all
            select 8 id, 'Acer' name, 2 region from dual)
            select id,name,region
            from (select id,name,region,
                   max(case when name = 'Acer'  then 1 else 0 end) over(partition by id)
                  *max(case when name = 'Fagus' then 1 else 0 end) over(partition by id) as willOut
                    from mydatenview
                   where name in('Acer','Fagus'))
            where willOut=1;
            and similar threads
            How can I apply query for criteria?
            simple select?
            Help Needed to write select statement
            • 18. Re: Select only records with same Id
              597990
              @ Aketi Jyuuzou

              THANK YOU VERY MUCH!
              That's the query that make my day. ;-)
              select id,name,region
              from (select id,name,region,
                     max(case when name = 'Acer'  then 1 else 0 end) over(partition by id)
                    *max(case when name = 'Fagus' then 1 else 0 end) over(partition by id) as willOut
                      from mydatenview
                     where name in('Acer','Fagus'))
              where willOut=1;
              How do I need to change that query if I want to search for 3 different names instead of 2 names ('Acer' or 'Fagus').
              Let's say: 'Acer' or 'Fagus' or 'Silvatica'
              • 19. Re: Select only records with same Id
                Nicolas.Gasparotto
                That should be hard to think, add a line such
                *max(case when name = 'Silvatica' then 1 else 0 end) over(partition by id)
                and modify the where clause.
                And so on.

                Nicolas.
                • 20. Re: Select only records with same Id
                  Aketi Jyuuzou
                  select id,name,region
                  from (select id,name,region,
                         max(case when name = 'Acer'  then 1 else 0 end) over(partition by id)
                        *max(case when name = 'Fagus' then 1 else 0 end) over(partition by id)
                        *max(case when name = 'Silvatica' then 1 else 0 end) over(partition by id) as willOut
                          from mydatenview
                         where name in('Acer','Fagus','Silvatica'))
                  where willOut=1;
                  using Nicolas's query
                  select id,name,region
                    from (select id,name,region,
                          count(distinct name) over(partition by id) cnt
                            from mydatenview
                           where name in('Acer','Fagus','Silvatica'))
                  where cnt = 3;
                  • 21. Re: Select only records with same Id
                    597990
                    Thank you very much!
                    1 2 Previous Next