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!

how to get first record alone while using disinct,,?

Discussion in 'SQL PL/SQL' started by Vicky, Jul 21, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    The query below retrieves,

    SELECT DISTINCT emp_name, CITY from employees;

    EMP_NAME CITY
    -------------------------------------------------- ----------
    elisa Delhi
    bala Chennai
    elisa Mumbai
    arun Mumbai
    Ram Chennai
    Ram Delhi
    celin Delhi
    Dev Chennai
    McCullam Mumbai
    Panner Mumbai

    10 rows selected

    I don't want emp_name to be repeated, it has to skip the entry if it has the same emp_name, How can we do this.,
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    http://www.oracle-base.com/articles/misc/analytic-functions.php
    For simple example (simply your_table replace with the table employees):
    Code (SQL):

    WITH your_data AS (
    SELECT 'elisa' emp_name, 'Delhi' city FROM dual UNION ALL
    SELECT 'bala','Chennai' FROM dual UNION ALL
    SELECT 'elisa','Mumbai' FROM dual UNION ALL
    SELECT 'arun','Mumbai' FROM dual UNION ALL
    SELECT 'Ram','Chennai' FROM dual UNION ALL
    SELECT 'Ram','Delhi' FROM dual UNION ALL
    SELECT 'celin','Delhi' FROM dual
     )
    ,RESULT AS (
         SELECT
               yd.emp_name,
               yd.city,
               ROW_NUMBER() OVER (partition BY emp_name ORDER BY city) rn
         FROM your_data yd
         )
    SELECT
          rs.*
    FROM RESULT rs
    WHERE rn = 1;

     

    First Last

    Code (SQL):

    WITH your_data AS (
    SELECT 'elisa' emp_name, 'Delhi' city FROM dual UNION ALL
    SELECT 'bala','Chennai' FROM dual UNION ALL
    SELECT 'elisa','Mumbai' FROM dual UNION ALL
    SELECT 'arun','Mumbai' FROM dual UNION ALL
    SELECT 'Ram','Chennai' FROM dual UNION ALL
    SELECT 'Ram','Delhi' FROM dual UNION ALL
    SELECT 'celin','Delhi' FROM dual
     )
     SELECT
           yd.emp_name,
           MAX(yd.city) keep(dense_rank FIRST ORDER BY yd.city) city
     FROM your_data yd
     GROUP BY
           yd.emp_name ;



     
     
    Vicky likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    Another option to add to Sergey's already mentioned 2 ways of doing it and also using analytic function: LAG

    Code (SQL):
    WITH your_data AS (
    SELECT 'elisa' emp_name, 'Delhi' city FROM dual UNION ALL
    SELECT 'bala','Chennai' FROM dual UNION ALL
    SELECT 'elisa','Mumbai' FROM dual UNION ALL
    SELECT 'arun','Mumbai' FROM dual UNION ALL
    SELECT 'Ram','Chennai' FROM dual UNION ALL
    SELECT 'Ram','Delhi' FROM dual UNION ALL
    SELECT 'celin','Delhi' FROM dual
     )
     SELECT y.emp_name, y.city FROM
     (
     SELECT
           yd.emp_name,
           city,
           LAG(emp_name, 1, 0) OVER (ORDER BY emp_name) prev_ename
     FROM your_data yd
     GROUP BY
           yd.emp_name, yd.city) y
     WHERE y.emp_name != y.prev_ename
     ;
     
    Vicky likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're using 11.2 why not use listagg?


    Code (SQL):

    SQL> WITH your_data AS (
      2  SELECT 'elisa' emp_name, 'Delhi' city FROM dual UNION ALL
      3  SELECT 'bala','Chennai' FROM dual UNION ALL
      4  SELECT 'elisa','Mumbai' FROM dual UNION ALL
      5  SELECT 'arun','Mumbai' FROM dual UNION ALL
      6  SELECT 'Ram','Chennai' FROM dual UNION ALL
      7  SELECT 'Ram','Delhi' FROM dual UNION ALL
      8  SELECT 'celin','Delhi' FROM dual
      9   )
     10  SELECT emp_name, listagg(city,',') WITHIN GROUP (ORDER BY emp_name) locs
     11  FROM your_data
     12  GROUP BY emp_name;


    EMP_N LOCS
    ----- ------------------------------------------------------------
    Ram   Chennai,Delhi
    arun  Mumbai
    bala  Chennai
    celin Delhi
    elisa Delhi,Mumbai


    SQL>
     
     
    Vicky likes this.