Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    12

    Unanswered: sql: GROUP BY trouble

    how can i do the following query
    select max(sal), deptname, deptno from department group by dept;
    ?
    I know that this query is not allowed. correct is
    select max(sal) from department group by dept;
    but what if I want to include deptnam and deptno in result?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select max(sal), deptname, deptno
    from department
    group by deptname, deptno
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    12
    Quote Originally Posted by r937
    select max(sal), deptname, deptno
    from department
    group by deptname, deptno
    It doesn't work
    SELECT DEPTNAME, DEPTNO, MAX(SAL) GROUP BY DEPTNAME, DEPTNO;
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    SQL> select * from a;
    
            ID      VALUE
    ---------- ----------
             1         10
             1         20
             2         40
             2         50
    
    SQL> select id, max(value)
      2  from a
      3  group by id;
    
            ID MAX(VALUE)
    ---------- ----------
             1         20
             2         50
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stasik_du
    SELECT DEPTNAME, DEPTNO, MAX(SAL) GROUP BY DEPTNAME, DEPTNO
    the reason this didn't work is because you forgot the FROM clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2004
    Posts
    12
    Quote Originally Posted by r937
    the reason this didn't work is because you forgot the FROM clause
    I have included it but did not paste in reply.

  7. #7
    Join Date
    Oct 2004
    Posts
    12
    Quote Originally Posted by r123456
    Code:
    SQL> select * from a;
    
            ID      VALUE
    ---------- ----------
             1         10
             1         20
             2         40
             2         50
    
    SQL> select id, max(value)
      2  from a
      3  group by id;
    
            ID MAX(VALUE)
    ---------- ----------
             1         20
             2         50
    Yes. Exactly. Now imagine that you have the same table but with another attribute date so it looks like Table a(id,value,date). And now you need to do the same
    select id, max(value)
    from a
    group by id;
    but also include dates in output. How would you do that?
    select id, max(value),date
    from a
    group by id;

    doesn't work.
    neither

    select id, max(value),date
    from a
    group by id, date;

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nor should it work

    when you use GROUP BY, you cannot "also include" another column

    that's the whole point of grouping

    now, what you seem to want is "the row which has the max(value) in its group"

    you started out asking how to do grouping, and i think this is what threw us off the track

    you can achieve what you want with grouping, but it requires a self join, and isn't very pretty

    a different way to approach it, which is a bit more elegant, and which also makes sense once you're familiar with it, is the correlated subquery:
    Code:
    select t1.id
         , t1.value
         , t1.date
      from yourtable as t1
     where t1.value
         = ( select max(value)
               from yourtable
              where id = t1.id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2004
    Posts
    12
    Quote Originally Posted by r937
    nor should it work

    when you use GROUP BY, you cannot "also include" another column

    that's the whole point of grouping

    now, what you seem to want is "the row which has the max(value) in its group"

    you started out asking how to do grouping, and i think this is what threw us off the track

    you can achieve what you want with grouping, but it requires a self join, and isn't very pretty

    a different way to approach it, which is a bit more elegant, and which also makes sense once you're familiar with it, is the correlated subquery:
    Code:
    select t1.id
         , t1.value
         , t1.date
      from yourtable as t1
     where t1.value
         = ( select max(value)
               from yourtable
              where id = t1.id )
    thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •