Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Select Statement....

    All,

    I have two tables EMP and DEPT with the following values:

    EMP
    -----

    Empno Deptno
    101 10
    102 20
    103 20
    104 30

    DEPT
    -------

    Deptno Dname
    10 AA
    20 BB
    30 CC


    My requerment is like below, if a Deparment is having only employee then display that Empno and if more than one employee then display as 'MORE':

    Deptno Empno
    10 101
    20 MORE
    30 104

    Regards,
    JD
    Last edited by jayanta_deb; 04-11-06 at 01:54.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try:
    Code:
    select deptno
    ,      case when count(*) = 1 then to_char(max(empno)) else 'MORE' end
    from emp
    group by deptno;

Posting Permissions

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