This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 1, 2013 6:05 AM by 988573 RSS

Group by help please

user12050217 Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Karthick any other way of doing it?
  • 3. Re: Group by help please
    Karthick_Arp Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

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