1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Get unique values ....

Discussion in 'SQL PL/SQL' started by alisondmurray, Apr 21, 2018.

  1. alisondmurray

    alisondmurray Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    USA
    hi,
    from foll. select, how can i get unique values for screen_type and screen

    select a.EMP_NO,a.SCREEN, b.SCREEN_TYPE
    from EMPLOYEE_TAB a, SCREEN_TAB b
    where a.id = b.ID
    and SCREEN_TYPE like '%S';

    EMP_ID SCREEN SCREEN_TYPE
    EMP_123 SCR100 SCRTYPE100S
    EMP_124 SCR100 SCRTYPE100S
    EMP_125 SCR100 SCRTYPE100S
    EMP_127 SCR102 SCRTYPE102S
    EMP_128 SCR102 SCRTYPE102S
    EMP_135 SCR102 SCRTYPE102S
    EMP_136 SCR102 SCRTYPE102Sv
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can't with that query as EMP_NO is unique. You could use listing():

    Code (SQL):
    SQL>  SELECT listagg(a.emp_no,',') WITHIN GROUP (ORDER BY a.screen) emp_nos, a.screen, b.screen_type
      2  FROM employee_tab a, screen_tab b
      3  WHERE a.id = b.id
      4   AND b.screen_type LIKE '%S'
      5* GROUP BY a.screen, b.screen_type
    SQL> /

    EMP_NOS                              SCREEN      SCREEN_TYPE
    ------------------------------------------------------------ ------------ ------------
    EMP_123,EMP_124,EMP_125,EMP_126                  SCR100      SCRTYPE100S
    EMP_127,EMP_128,EMP_135,EMP_136                  SCR102      SCRTYPE102S

    SQL>