Forum Stats

  • 3,734,032 Users
  • 2,246,861 Discussions
  • 7,857,003 Comments

Discussions

Multiple distinct

662385
662385 Member Posts: 25
edited November 2008 in SQL & PL/SQL
I wish to Have many cols with the distinct function attached in one statement if possible, I think the closest to this may be the union but it is all returned in one column. any possibility that you can get it to show in multiple columns? eg i have(animal col) dog dog cat (house number col) 13 23 17 21 23 so they show dog cat in one col 12 23 17 21 and the numbers in another
I am using oracle 11g

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 40,986 Red Diamond
    Can you give us an example of your input data and expected output. It's not clear what you mean.

    Use {noformat}
    {noformat} tags around your data and/or code to keep the formatting.                                                                                                                                                                                                                                                                                                                                                                                            
  • BluShadow
    BluShadow Member, Moderator Posts: 40,986 Red Diamond
    Do you mean like this?
    SQL> select * from tab1;
    
    ANIMAL          HOUSE
    ---------- ----------
    dog                10
    dog                20
    dog                40
    cat                30
    cat                10
    rabbit             10
    rabbit             50
    
    7 rows selected.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  select animal, house
      2  from (select animal, row_number() over (order by animal) rn from (select distinct animal from tab1)) an
      3       FULL OUTER JOIN
      4       (select house, row_number() over (order by house) rn from (select distinct house from tab1)) hs
      5       ON (an.rn = hs.rn)
      6* order by coalesce(an.rn,hs.rn)
    SQL> /
    
    ANIMAL          HOUSE
    ---------- ----------
    cat                10
    dog                20
    rabbit             30
                       40
                       50
    
    SQL>
  • 662385
    662385 Member Posts: 25
    say i have 2 columns
    animal House Number
    dog 12
    Cat 13
    Hamster 13
    Dog 11
    Cat 13

    What i want returned is something like the following, the data has no correlation what so ever.
    dog 13
    cat 11
    hanster 12
    i was thinking select Distinct animal from zoo
    union
    select distinct number from house
    this returns something along the lines of
    dog
    cat
    hamster
    13
    11
    12
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    What i want returned is something like the following, the data has no correlation what so ever.
    dog 13
    cat 11
    hanster 12
    On what logic do you want to see that?

    Not getting your requirement clearly at all.

    Regards.

    Satyaki De.
  • 662385
    662385 Member Posts: 25
    ok best way to think about this is, i have seven col's I need them all to have the distinct statement on them. Is it possible to do this in one Statement.
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    I didn't understand your question correctly. But, you can check this - i'm not sure whether this is right or wrong for you ->
    select distinct col1
    from tab1
    union all
    select distinct col2
    from tab1
    union all
    select distinct col3
    from tab1
    . . . . . 
    union all
    select distinct col7
    from tab1;
    Regards.

    Satyaki De.
  • 662385
    662385 Member Posts: 25
    Partly there, That returns all the correct data in one col, iwould like to return it over multple cols
  • 662385
    662385 Member Posts: 25
    Could i do this with more than 2 Sorry i don't fully understand how your ststement does what it has done!
  • John Spencer
    John Spencer Member Posts: 8,567
    Not usre why you want this but something like?
    select distinct col1, null col2, null col3, ..., null col7
    from tab1
    union all
    select distinct null, col2, null, ..., null
    from tab1
    union all
    select distinct null, null, col3, ..., null
    from tab1
    . . . . . 
    union all
    select distinct null, null, null, ..., col7
    from tab1;
    John
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    In a different way ->
    satyaki>
    satyaki>select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
    PL/SQL Release 10.2.0.3.0 - Production
    CORE    10.2.0.3.0      Production
    TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production
    
    Elapsed: 00:00:00.35
    satyaki>
    satyaki>create table animal_details
      2    (
      3      animal         varchar2(30),
      4      House_Number   number(5)
      5    );
    
    Table created.
    
    Elapsed: 00:00:04.27
    satyaki>
    satyaki>insert into animal_details values('&animal',&House_Number);
    Enter value for animal: dog
    Enter value for house_number: 12
    old   1: insert into animal_details values('&animal',&House_Number)
    new   1: insert into animal_details values('dog',12)
    
    1 row created.
    
    Elapsed: 00:00:00.19
    satyaki>
    satyaki>/
    Enter value for animal: cat
    Enter value for house_number: 13
    old   1: insert into animal_details values('&animal',&House_Number)
    new   1: insert into animal_details values('cat',13)
    
    1 row created.
    
    Elapsed: 00:00:00.00
    satyaki>
    satyaki>/
    Enter value for animal: Hamster
    Enter value for house_number: 13
    old   1: insert into animal_details values('&animal',&House_Number)
    new   1: insert into animal_details values('Hamster',13)
    
    1 row created.
    
    Elapsed: 00:00:00.11
    satyaki>
    satyaki>/
    Enter value for animal: Dog
    Enter value for house_number: 11
    old   1: insert into animal_details values('&animal',&House_Number)
    new   1: insert into animal_details values('Dog',11)
    
    1 row created.
    
    Elapsed: 00:00:00.03
    satyaki>
    satyaki>/
    Enter value for animal: cat
    Enter value for house_number: 13
    old   1: insert into animal_details values('&animal',&House_Number)
    new   1: insert into animal_details values('cat',13)
    
    1 row created.
    
    Elapsed: 00:00:00.03
    satyaki>
    satyaki>commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.05
    satyaki>
    satyaki>
    satyaki>select * from animal_details;
    
    ANIMAL                         HOUSE_NUMBER
    ------------------------------ ------------
    dog                                      12
    cat                                      13
    Hamster                                  13
    Dog                                      11
    cat                                      13
    
    Elapsed: 00:00:00.35
    satyaki>
    satyaki>
    satyaki>
    satyaki>create or replace type animal_obj as object
      2      (
      3         a_mal        varchar2(30),
      4         h_num        number(5)
      5      );
      6  /
    
    Type created.
    
    Elapsed: 00:00:08.08
    satyaki>
    satyaki>
    satyaki>create or replace type animal_rec as table of animal_obj;
      2  /
    
    Type created.
    
    Elapsed: 00:00:00.74
    satyaki>
    satyaki>
    satyaki>create or replace function animal_sel
      2  return animal_rec 
      3  pipelined 
      4  is
      5    cursor c1
      6    is
      7      select distinct ANIMAL al
      8      from ( 
      9              select upper(animal) animal
     10              from animal_details
     11           );
     12  
     13    r1 c1%rowtype;
     14  
     15    cursor c2
     16    is
     17      select distinct HOUSE_NUMBER hn
     18      from animal_details;
     19  
     20    r2 c2%rowtype;
     21  begin
     22   open c1;
     23   open c2;
     24   loop
     25     fetch c1 into r1;
     26     fetch c2 into r2;
     27  
     28       exit when c1%notfound and c2%notfound;
     29  
     30       if c1%notfound then
     31           r1:= null;
     32       elsif c2%notfound then
     33           r2:= null;
     34       end if;
     35  
     36       pipe row(animal_obj(r1.al,r2.hn));
     37  
     38    end loop;
     39    close c2;
     40    close c1;
     41  
     42  return;
     43  end;
     44  /
    
    Function created.
    
    Elapsed: 00:00:00.05
    satyaki>
    satyaki>select * from table(cast(animal_sel as animal_rec));
    
    A_MAL                               H_NUM
    ------------------------------ ----------
    CAT                                    13
    HAMSTER                                11
    DOG                                    12
    
    Elapsed: 00:00:00.09
    satyaki>
    Regards.

    Satyaki De.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    edited November 2008
    Hi,

    Do you want
    the first value of each column to appear in row 1,
    the second value (if any) in row 2, ...,
    the n-th value (if any) in row n ...?

    If so, here's one way:
    WITH	animal	AS
    (
    	SELECT DISTINCT
    		animal
    	,	DENSE_RANK () OVER (ORDER BY animal)	AS animal_rank
    	FROM	zoo
    )
    ,	house	AS
    (
    	SELECT DISTINCT
    		house
    	,	DENSE_RANK () OVER (ORDER BY house)	AS house_rank
    	FROM	zoo
    )
    ,	cntr	AS
    (
    	SELECT	LEVEL	AS n
    	FROM	dual
    	CONNECT BY	LEVEL	<= GREATEST 
    					( (SELECT COUNT (*) FROM animal)
    					, (SELECT COUNT (*) FROM house)
    					)
    )
    SELECT	animal
    ,	house
    FROM		cntr
    LEFT OUTER JOIN	animal	ON n = animal_rank
    LEFT OUTER JOIN	house	ON n = house_rank
    ORDER BY	n;
    To add aditional columns:
    (a) add a new sub-query, like the one that starts ", house AS"
    (b) add a new line to the cntr sub-query, like " , (SELECT COUNT (*) FROM house)"
    (c) add a new line to the SELECT-clause, like ", house"
    (d) add a new line to the FROM-clause, like "LEFT OUTER JOIN house ON n = house_rank"
    but instead of "house", use the new column name.
    Frank Kulash
  • 662385
    662385 Member Posts: 25
    Looking at this a different way is there any way i can pass a column name in as a variable. For instance
    "COL_NAME" IN VARCHAR2

    SELECT DISTINCT COL_NAME FROM ZOO;

    This way is suitable for me i am still trying out the other ways but they seem a little more complex than this idea IF you can do this in oracle
  • BluShadow
    BluShadow Member, Moderator Posts: 40,986 Red Diamond
    user10372910 wrote:
    Could i do this with more than 2 Sorry i don't fully understand how your ststement does what it has done!
    Yes you can...
    SQL> select * from tab1;
    
    ANIMAL          HOUSE       FRED
    ---------- ---------- ----------
    dog                10          2
    dog                20          1
    dog                40          2
    cat                30          4
    cat                10          3
    rabbit             10          2
    rabbit             50          1
    
    7 rows selected.
    
    SQL> select animal, house, fred
      2  from (select animal, row_number() over (order by animal) rn from (select distinct animal from tab1)) an
      3       FULL OUTER JOIN
      4       (select house, row_number() over (order by house) rn from (select distinct house from tab1)) hs
      5       ON (an.rn = hs.rn)
      6       FULL OUTER JOIN
      7       (select fred, row_number() over (order by fred) rn from (select distinct fred from tab1)) fr
      8       ON (COALESCE(an.rn,hs.rn) = fr.rn)
      9  order by coalesce(an.rn,hs.rn,fr.rn)
     10  /
    
    ANIMAL          HOUSE       FRED
    ---------- ---------- ----------
    cat                10          1
    dog                20          2
    rabbit             30          3
                       40          4
                       50
    
    SQL>
    Essentially what it's doing is that it's selecting the distinct values for each column as sub selects and assigning a row number to each record in each sub select.
    It is then joining each of those sub selects together using the row number as the joining column. The FULL OUTER JOIN allows the join to happen where there are a different number of distinct values in each of the sets so that we get as many rows returned as the maximum distinct values in any one of the sets.
    The order by clause just orders based on the row numbers, taking whichever row number is present in any of the sets.
  • BluShadow
    BluShadow Member, Moderator Posts: 40,986 Red Diamond
    To make it a little more readable, it's possible to use the dense_rank() analytical function to assign a virtual row number to the distinct values. This removes the sub-sub-selects. ;)

    Firstly look at the sub selects by themselves...
    SQL> select distinct animal, dense_rank() over (order by animal) rn from tab1;
    
    ANIMAL             RN
    ---------- ----------
    cat                 1
    dog                 2
    rabbit              3
    
    SQL> select distinct house, dense_rank() over (order by house) rn from tab1;
    
         HOUSE         RN
    ---------- ----------
            30          3
            40          4
            20          2
            10          1
            50          5
    
    SQL> select distinct fred, dense_rank() over (order by fred) rn from tab1;
    
          FRED         RN
    ---------- ----------
             4          4
             1          1
             2          2
             3          3
    So there you can see each select statement obtains the distinct values for the specified column and assigns a "row number" (rn) to each row based on the order of the data itself.

    If we then combine those using FULL OUTER JOINs, joining on the RN value, we get...
    SQL> select animal, house, fred
      2  from (select distinct animal, dense_rank() over (order by animal) rn from tab1) an
      3       FULL OUTER JOIN
      4       (select distinct house, dense_rank() over (order by house) rn from tab1) hs
      5       ON (an.rn = hs.rn)
      6       FULL OUTER JOIN
      7       (select distinct fred, dense_rank() over (order by fred) rn from tab1) fr
      8       ON (COALESCE(an.rn,hs.rn) = fr.rn)
      9  order by coalesce(an.rn,hs.rn,fr.rn)
     10  /
    
    ANIMAL          HOUSE       FRED
    ---------- ---------- ----------
    cat                10          1
    dog                20          2
    rabbit             30          3
                       40          4
                       50
    
    SQL>
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2008
    My funny solution B-)
    create table tab1(ANIMAL,HOUSE,FRED) as
    select 'dog'   ,10,2 from dual union all
    select 'dog'   ,20,1 from dual union all
    select 'dog'   ,40,2 from dual union all
    select 'cat'   ,30,4 from dual union all
    select 'cat'   ,10,3 from dual union all
    select 'rabbit',10,2 from dual union all
    select 'rabbit',50,1 from dual;
    
    select ANIMAL,HOUSE,FRED
    from (select ANIMAL,HOUSE,FRED
            from tab1
           model
           dimension by (RowNum as soeji,
                         dense_rank() over(order by ANIMAL) as Rn1,
                         dense_rank() over(order by HOUSE)  as Rn2,
                         dense_rank() over(order by FRED)   as Rn3)
           measures(ANIMAL,HOUSE,FRED,
                    ANIMAL a,HOUSE h,FRED f)
           rules(
           ANIMAL[any,any,any,any] = max(a)[any,cv(soeji),any,any],
           HOUSE[any,any,any,any]  = max(h)[any,any,cv(soeji),any],
           FRED[any,any,any,any]   = max(f)[any,any,any,cv(soeji)]))
     where ANIMAL is not null
        or nvl(HOUSE,FRED) is not null
    order by 1,2,3;
    
    ANIMAL  HOUSE  FRED
    ------  -----  ----
    cat        10     1
    dog        20     2
    rabbit     30     3
    null       40     4
    null       50  null
  • BluShadow
    BluShadow Member, Moderator Posts: 40,986 Red Diamond
    edited November 2008
    Aketi Jyuuzou wrote:
    ANIMAL  HOUSE  FRED
    ------  -----  ----
    cat        10     1
    dog        20     2
    rabbit     30     3
    null       40     4
    null     null  null
    What happened to house 50? Don't tell me, the world credit crisis has caused it to be reposessed ;)
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    What happened to house 50?
    OOPS ?:|
    I do not know that why that row included in my post.
    I fixed it.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    What i want returned is something like the following, the data has no correlation what so ever.
    dog 13
    cat 11
    hanster 12
    Probably not exactly the expected output, but it might be interesting to know anyway:
    SQL> select collect(distinct job) job, collect (distinct deptno) deptno from emp;
    
    JOB                                                DEPTNO
    -------------------------------------------------- -------------------------------------------------
    -
    SYSTPWt1bDr8w/sjgQAB/AQAgvA==('ANALYST', 'CLERK',  SYSTPWt1bDr8m/sjgQAB/AQAgvA==(10, 20, 30)
    'MANAGER', 'PRESIDENT', 'SALESMAN')
This discussion has been closed.