Results 1 to 3 of 3

Thread: sub qry

  1. #1
    Join Date
    Nov 2002
    Posts
    22

    Unanswered: sub qry

    HI there have 2 tbls
    dept (deptno(pk), dname(varcahr), loc(varchar)
    emp(empno(pk), deptno(fk), sal, job, ename)

    for each loc.. need to display emploname, his job & who has the highest sal.
    I can do by deptno, but not by loc pl shelp!

    **
    select deptno, ename, job, sal
    from emp
    where (deptno, sal) IN
    (select deptno, max(sal) from emp group by deptno)


    DEPTNO ENAME JOB SAL
    ---------- ---------- --------- ----------
    10 KING PRESIDENT 5000
    20 FORD ANALYST 3000
    20 SCOTT ANALYST 3000
    30 BLAKE MANAGER 2850

  2. #2
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    tables

    Table DEPT : dept #, dept name, location
    Table EMP: emp #,department #, sal, job, emp name

    for emp by deptno, ordered by salary
    select a.deptno, b.empname, b.job, b.sal
    from dept a, emp b
    where a.deptno = b.deptno
    order by b.sal;

    for emp by loc, ordered by salary
    select a.loc, b.empname, b.job, b.sal
    from dept a, emp b
    where a.deptno = b.deptno
    order by b.sal;


    for emp by loc, ordered by loc
    select a.loc, b.empname, b.job, b.sal
    from dept a, emp b
    where a.deptno = b.deptno
    order by a.loc;


    How does that work for you??
    Michellea Southern-David

  3. #3
    Join Date
    Oct 2003
    Posts
    26

    Re: tables

    Is this what your are looking for ?


    select e2.ename,e2.empno,e2.sal,e2.deptno from emp e2,dept d2
    where d2.deptno=e2.deptno
    AND (d2.loc,e2.sal) in (select d1.loc,max(e1.sal) from emp e1,dept d1
    where d1.deptno=e1.deptno
    group by d1.loc
    )

    HTH
    Arvind

Posting Permissions

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