1 2 Previous Next 18 Replies Latest reply: Feb 1, 2013 8:05 AM by 988573 RSS

    Group by help please

    user12050217
      Oracle 11.2.0.1
      Windows xp

      create table cust(cust_id number,phone number,city varchar2(20),state varchar2(20),country varchar2(20));
      insert into cust values (101,12345,'City1','State1','Country1');
      insert into cust values (102,1114,'City1','State1','Country1');
      insert into cust values (103,1214,'City2','State1','Country1');
      insert into cust values (104,44414,'City2','State1','Country1');
      insert into cust values (105,21454,'City2','State1','Country1');
      insert into cust values (106,12546,'City1','State2','Country1');
      insert into cust values (107,146,'City1','State2','Country1');
      insert into cust values (108,44569,'City1','State2','Country1');
      insert into cust values (109,26548,'City2','State2','Country1');
      insert into cust values (110,96545,'City3','State2','Country1');
      insert into cust values (111,945,'City1','State1','Country2');
      SQL> select * from cust;
      
         CUST_ID      PHONE CITY                 STATE                COUNTRY
      ---------- ---------- -------------------- -------------------- --------------------
             101      12345 City1                State1               Country1
             102       1114 City1                State1               Country1
             103       1214 City2                State1               Country1
             104      44414 City2                State1               Country1
             105      21454 City2                State1               Country1
             106      12546 City1                State2               Country1
             107        146 City1                State2               Country1
             108      44569 City1                State2               Country1
             109      26548 City2                State2               Country1
             110      96545 City3                State2               Country1
             111        945 City1                State1               Country2
      
      11 rows selected.
      Required output : (If I wish to get State=State1 and Country=Country1) Only any one row from each city of country1 and State1.
         CUST_ID      PHONE CITY                 STATE                COUNTRY
      ---------- ---------- -------------------- -------------------- --------------------
             101      12345 City1                State1               Country1
             103       1214 City2                State1               Country1
      Required output : (If I wish to get State=State2 and Country=Country1) Only any one row from each city of country1 and State2.
         CUST_ID      PHONE CITY                 STATE                COUNTRY
      ---------- ---------- -------------------- -------------------- --------------------
             106      12546 City1                State2               Country1
             109      26548 City2                State2               Country1
             110      96545 City3                State2               Country1
      any row i.e. columns of the table group by country,state,city.

      Thanks.
        • 1. Re: Group by help please
          Karthick_Arp
          You can use ROW_NUMBER like this
          select cust_id, phone, city, state, country
            from (
                    select c.*, row_number() over(partition by city order by cust_id) rno
                      from cust c
                     where state = 'State1' and country = 'Country1'
                 )
           where rno = 1 ;      
           
          select cust_id, phone, city, state, country
            from (
                    select c.*, row_number() over(partition by city order by cust_id) rno
                      from cust c
                     where state = 'State2' and country = 'Country1'
                 )
           where rno = 1 ;       
          • 2. Re: Group by help please
            Rahul_India
            Karthick any other way of doing it?
            • 3. Re: Group by help please
              Karthick_Arp
              Rahul India wrote:
              Karthick any other way of doing it?
              Why another way? If you can tell the propose we can figure out a way.
              • 4. Re: Group by help please
                Girish Sharma
                May be this one :
                select * from cust where rowid in
                (
                select min(rowid) from cust
                where country='Country1' and state='State1'
                group by city
                )
                order by city
                But yes, you are right, in the above solution I am using rowid, while Karthick used row_number,over(...) clause; i.e. Oracle provided functions. If OP wants to get the output in different DBMS s/w like SQL Server,MS Access etc. then I don't know how a pure SQL only will do the job...

                Regards
                Girish Sharma
                • 5. Re: Group by help please
                  Karthick_Arp
                  But yes, you are right, in the above solution I am using rowid, while Karthick used row_number,over(...) clause; i.e. Oracle provided functions.
                  The main difference is not ROWID and ROW_NUMBER, Its the number of times the table is scanned.
                  If OP wants to get the output in different DBMS s/w like SQL Server,MS Access etc. then I don't know how a pure SQL only will do the job...
                  In that case OP should not be posting in this forum :)
                  • 6. Re: Group by help please
                    Rahul_India
                    Karthick_Arp wrote:
                    Rahul India wrote:
                    Karthick any other way of doing it?
                    Why another way? If you can tell the propose we can figure out a way.
                    without analytical functions.i dont know them.
                    i was looking for a query similar to Girish :)
                    • 7. Re: Group by help please
                      Karthick_Arp
                      without analytical functions.i dont know them.
                      Then you should learn. Finding an alternate just because you are not willing to learn is not an option.
                      i was looking for a query similar to Girish :)
                      That is not the correct way to do, People use to do like that ages ago.
                      • 8. Re: Group by help please
                        Rahul_India
                        >
                        That is not the correct way to do, People use to do like that ages ago.
                        You mean to say its not the optimal way to do right?
                        • 9. Re: Group by help please
                          Karthick_Arp
                          Rahul India wrote:
                          That is not the correct way to do, People use to do like that ages ago.
                          You mean to say its not the optimal way to do right?
                          Yes, You can say that. But i prefer to call it INCORRECT way of doing :)
                          • 10. Re: Group by help please
                            Rahul_India
                            lol
                            so is your approach is kind of generic way to get results where we want to select more columns than present in group by clause?
                            • 11. Re: Group by help please
                              Karthick_Arp
                              Rahul India wrote:
                              lol
                              so is your approach is kind of generic way to get results where we want to select more columns than present in group by clause?
                              I dont get what you are trying to say, But [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174]Analytic Functions has brought in lot of capabilities to Oracle SQL. I would suggest you to start learning them.
                              • 12. Re: Group by help please
                                BrendanP
                                Analytic functions are essential if you use SQL professionally. Here, you can also use grouping though.
                                SQL> SELECT Max (cust_id) KEEP (DENSE_RANK FIRST ORDER BY cust_id) cust_id,
                                  2         Max (phone) KEEP (DENSE_RANK FIRST ORDER BY cust_id) phone,
                                  3         city, state, country
                                  4    FROM cust
                                  5   GROUP BY country, state, city
                                  6   ORDER BY country, state, city, cust_id
                                  7  /
                                
                                CUST_ID      PHONE CITY       STATE      COUNTRY
                                ------- ---------- ---------- ---------- ----------
                                    101      12345 City1      State1     Country1
                                    103       1214 City2      State1     Country1
                                    106      12546 City1      State2     Country1
                                    109      26548 City2      State2     Country1
                                    110      96545 City3      State2     Country1
                                    111        945 City1      State1     Country2
                                
                                6 rows selected.
                                • 13. Re: Group by help please
                                  sql_coder
                                  BrendanP wrote:
                                  Analytic functions are essential if you use SQL professionally. Here, you can also use grouping though.
                                  no doubt analytic functions are very usefull but I would say you can use SQL professionally even without them. And they are not part of the SQL expert certification....

                                  Ikrischer
                                  • 14. Re: Group by help please
                                    BrendanP
                                    Well, I suppose technically you can, but I don't see how you can be SQL expert without them. On the other hand Oracle University do - you seem to be right about their certification:

                                    http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-047&p_org_id=1001&lang=US

                                    *(gobsmacked)*
                                    1 2 Previous Next