Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Unanswered: Help with Group by clause

    I have two tables

    First table

    mstr_dept

    Code:
      
      `deptid` int(11) NOT NULL primary key,
      `deptname` varchar(10) NOT NULL,
    Second table


    emp
    Code:
      
      `empid` int(11) NOT NULL primary key,
      `deptid` int(11) refrences mstr_dept(deptid)
      `name` varchar(30) NOT NULL,
      `salary` int(11) NOT NULL default '0',

    Now i have to select the maximum salary for each department

    for which i can use the query

    Code:
      
    SELECT deptid, MAX(salary) from emp group by deptid
    but now i want to know who is getting the maximum salary in each department. If i give the query

    Code:
      
    SELECT deptid, empid, MAX(salary) from emp group by deptid, empid
    i am not getting the correct answer. So any one can help me..
    Sudar

    --
    My Blog

  2. #2
    Join Date
    Aug 2002
    Location
    Germany
    Posts
    17
    Try something like this:

    select e.deptid, e.empid, e.name, e.salary from emp e, (SELECT deptid, MAX(salary) sal from emp group by deptid ) max_sal where e.deptid=max_sal.deptid and e.salary=max_sal.sal

  3. #3
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137
    Thanks sassermann for the reply.....
    Sudar

    --
    My Blog

Posting Permissions

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