Forum Stats

  • 3,733,252 Users
  • 2,246,738 Discussions
  • 7,856,634 Comments

Discussions

Distinct values in each column

439955
439955 Member Posts: 17
edited February 2008 in SQL & PL/SQL
Hi

I have a table with three columns and I would like to display distinct values in each column. Could any one please help me to get the output.


col1 col2 col3
==== ==== ====
a 1 x
a 2 y
a 3 z
a 4 u
b 5 v
b 6 x
b 7 x
b 9 x
b 10 y
b 11 y
b 12 y
b 13 y
b 14 x
b 15 y
b 16 z
b 17 u
b 18 v
b 19 x
b 20 x
c 21 x
c 22 y
c 23 y
c 24 y
c 25 y
c 26 x
c 27 y
c 28 z

Output needed
===========
col1 col2 col3
==== ==== ====
a 1 x
b 2 y
c 3 z
4 u
5 v
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Thanks
Vasanth

Comments

  • 439955
    439955 Member Posts: 17
    Oops.. Some formatting issue.. The result should be like this

    col2 col3 col1
    ==== ==== ====
    1 x a
    2 y b
    3 z c
    4 u
    5 v
    6
    7
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
  • RadhakrishnaSarma
    RadhakrishnaSarma Member Posts: 2,900
    edited February 2008
    Vasanth! You can paste the format between [pre] and [/pre] tags to preserve the spaces.

    Use
    break
    SQL*Plus command.


    Cheers
    Sarma.
  • Keith Jamieson
    Keith Jamieson Member Posts: 2,815
    Not quite finished but this should give you a huge start
    with distinct_cols 
    as
    ( select 
    'a' col1 , 1 col2 , 'x' col3 from dual
    union all select 
    'a', 2, 'y' from dual
    union all select 
    'a', 3, 'z' from dual
    union all select 
    'a', 4, 'u' from dual
    union all select 
    'b', 5, 'v' from dual
    union all select 
    'b', 6, 'x' from dual
    union all select 
    'b', 7, 'x' from dual
    union all select 
    'b', 9, 'x' from dual
    union all select 
    'b', 10, 'y' from dual
    union all select 
    'b', 11, 'y' from dual
    union all select 
    'b', 12, 'y' from dual
    union all select 
    'b', 13, 'y' from dual
    union all select 
    'b', 14, 'x' from dual
    union all select 
    'b', 15, 'y' from dual
    union all select 
    'b', 16, 'z' from dual
    union all select 
    'b', 17, 'u' from dual
    union all select 
    'b', 18, 'v' from dual
    union all select 
    'b', 19, 'x' from dual
    union all select 
    'b', 20, 'x' from dual
    union all select
    'c', 21, 'x' from dual
    union all select 
    'c', 22, 'y' from dual
    union all select 
    'c',23, 'y' from dual
    union all select 
    'c', 24, 'y' from dual
    union all select 
    'c', 25, 'y' from dual
    union all select 
    'c', 26, 'x' from dual
    union all select 
    'c', 27, 'y' from dual
    union all select 
    'c', 28, 'z' from dual
    )
    select decode(marker,'1',col1,NULL) col1_distinct,
           decode(marker,'2',col1,NULL) col2_distinct,
           decode(marker,'3',col1,NULL) col3_distinct
    from
    (
    select distinct '1' marker,col1  
    from distinct_cols 
    union all
    select distinct '2',to_char(col2)
    from distinct_cols 
    union all
    select distinct '3',col3 
    from distinct_cols 
    )
  • Alessandro Rossi
    Alessandro Rossi Member Posts: 1,057 Bronze Badge
    This is a solution but result is not deterministic, and this is also right because the properties of the row in the output are not completely defined.
    Processing ...
    with distinct_cols
    as
    ( select
    'a' col1 , 1 col2 , 'x' col3 from dual
    union all select
    'a', 2, 'y' from dual
    union all select
    'a', 3, 'z' from dual
    union all select
    'a', 4, 'u' from dual
    union all select
    'b', 5, 'v' from dual
    union all select
    'b', 6, 'x' from dual
    union all select
    'b', 7, 'x' from dual
    union all select
    'b', 9, 'x' from dual
    union all select
    'b', 10, 'y' from dual
    union all select
    'b', 11, 'y' from dual
    union all select
    'b', 12, 'y' from dual
    union all select
    'b', 13, 'y' from dual
    union all select
    'b', 14, 'x' from dual
    union all select
    'b', 15, 'y' from dual
    union all select
    'b', 16, 'z' from dual
    union all select
    'b', 17, 'u' from dual
    union all select
    'b', 18, 'v' from dual
    union all select
    'b', 19, 'x' from dual
    union all select
    'b', 20, 'x' from dual
    union all select
    'c', 21, 'x' from dual
    union all select
    'c', 22, 'y' from dual
    union all select
    'c',23, 'y' from dual
    union all select
    'c', 24, 'y' from dual
    union all select
    'c', 25, 'y' from dual
    union all select
    'c', 26, 'x' from dual
    union all select
    'c', 27, 'y' from dual
    union all select
    'c', 28, 'z' from dual
    )
    select col1,col2,col3
    from (
    select rownum rn
    from distinct_cols
    ) natural left outer join (
    select rownum rn,col1
    from (
    select distinct col1
    from distinct_cols
    )
    ) natural left outer join (
    select rownum rn,col2
    from (
    select distinct col2
    from distinct_cols
    )
    ) natural left outer join (
    select rownum rn,col3
    from (
    select distinct col3
    from distinct_cols
    )
    )
    Query finished, retrieving results...
    COL1 COL2 COL3
    ---- ----- -----
    a 6 z
    b 7 x
    c 12 y
    17 u
    25 v
    10
    11
    21
    16
    2
    1
    28
    13
    20
    26
    4
    15
    3
    18
    23
    27
    24
    9
    5
    14
    22
    19

    27 row(s) retrieved
    Bye Alessandro
  • 439955
    439955 Member Posts: 17
    Thanks to every one... Sarma,Keith Jamieson and Alessandro .

    I hopeI can retrive the result now. Thanks again

    Vasanth
  • 121256
    121256 Member Posts: 1,054
    edited February 2008
    No joins, just analytics:
    exec dbms_random.seed(0)
    with t as ( select chr(ascii('a') + dbms_random.value(0, 3)) as c1,
    trunc(dbms_random.value(1, 20)) as c2,
    chr(ascii('u') + dbms_random.value(0, 6)) as c3
    from dual connect by level <= 20
    ),
    -------------------------------------------------------------------
    tt1 as ( select lag( null, 1, c1) over (partition by c1 order by null) as c1,
    lag(to_number(null), 1, c2) over (partition by c2 order by null) as c2,
    lag( null, 1, c3) over (partition by c3 order by null) as c3
    from t
    ),
    tt2 as ( select tt1.*,
    row_number() over (order by c1) as rn1,
    row_number() over (order by c2) as rn2,
    row_number() over (order by c3) as rn3
    from tt1
    ),
    tt3 as ( select case when rn1 > rn2
    then last_value(c1) over (order by rn1 range between abs(rn1 - rn2) preceding
    and abs(rn1 - rn2) preceding)
    else last_value(c1) over (order by rn1 range between abs(rn2 - rn1) following
    and abs(rn2 - rn1) following)
    end as c1,
    c2,
    case when rn3 > rn2
    then last_value(c3) over (order by rn3 range between abs(rn3 - rn2) preceding
    and abs(rn3 - rn2) preceding)
    else last_value(c3) over (order by rn3 range between abs(rn2 - rn3) following
    and abs(rn2 - rn3) following)
    end as c3
    from tt2
    )
    select c1, c2, c3 from tt3
    where c1 || c2 || c3 is not null
    order by c1, c2, c3
    ;

    C1 C2 C3
    -- ------------- --
    a 1 u
    b 3 v
    c 4 w
    5 x
    7 y
    8 z
    9
    11
    13
    15
    16
    17
    19

    13 rows selected.
  • 572471
    572471 Member Posts: 984 Green Ribbon
    No joins, just analytics:
    nice, but the only drawback - you need to know the column, comprising the biggest amount of distinct values beforehand.
  • 121256
    121256 Member Posts: 1,054
    but the only drawback - you need to know the column, comprising the biggest amount of distinct values beforehand.
    No. There is no any difference in which column will be chosen as "based".
    For demo purposes I've added extra row number column to show full generality and symmetry of the approach:
    exec dbms_random.seed(0)
    with t as ( select chr(ascii('a') + dbms_random.value(0, 3)) as c1,
    trunc(dbms_random.value(1, 20)) as c2,
    chr(ascii('u') + dbms_random.value(0, 6)) as c3
    from dual connect by level <= 20
    ),
    -------------------------------------------------------------------
    tt1 as ( select lag( null, 1, c1) over (partition by c1 order by null) as c1,
    lag(to_number(null), 1, c2) over (partition by c2 order by null) as c2,
    lag( null, 1, c3) over (partition by c3 order by null) as c3
    from t
    ),
    tt2 as ( select tt1.*,
    row_number() over (order by c1) as rn1,
    row_number() over (order by c2) as rn2,
    row_number() over (order by c3) as rn3,
    rownum rn
    from tt1
    ),
    tt3 as ( select case when rn1 > rn
    then last_value(c1) over (order by rn1 range between abs(rn1 - rn) preceding
    and abs(rn1 - rn) preceding)
    else last_value(c1) over (order by rn1 range between abs(rn - rn1) following
    and abs(rn - rn1) following)
    end as c1,
    case when rn2 > rn
    then last_value(c2) over (order by rn2 range between abs(rn2 - rn) preceding
    and abs(rn2 - rn) preceding)
    else last_value(c2) over (order by rn2 range between abs(rn - rn2) following
    and abs(rn - rn2) following)
    end as c2,
    case when rn3 > rn
    then last_value(c3) over (order by rn3 range between abs(rn3 - rn) preceding
    and abs(rn3 - rn) preceding)
    else last_value(c3) over (order by rn3 range between abs(rn - rn3) following
    and abs(rn - rn3) following)
    end as c3
    from tt2
    )
    select c1, c2, c3 from tt3
    where c1 || c2 || c3 is not null
    order by c1, c2, c3
    ;

    C1 C2 C3
    -- ------------- --
    a 1 u
    b 3 v
    c 4 w
    5 x
    7 y
    8 z
    9
    11
    13
    15
    16
    17
    19

    13 rows selected.
    P.S. Thanks to [url http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=482506&hl=over+range#4785373]Vladimir Sitnikov for the demo of the approach.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited February 2008
    create table ColTable(Col1,Col2,Col3) as
    select 'a', 1,'x' from dual union all
    select 'a', 2,'y' from dual union all
    select 'a', 3,'z' from dual union all
    select 'a', 4,'u' from dual union all
    select 'b', 5,'v' from dual union all
    select 'b', 6,'x' from dual union all
    select 'b', 7,'x' from dual union all
    select 'b', 9,'x' from dual union all
    select 'b',10,'y' from dual union all
    select 'b',11,'y' from dual union all
    select 'b',12,'y' from dual union all
    select 'b',13,'y' from dual union all
    select 'b',14,'x' from dual union all
    select 'b',15,'y' from dual union all
    select 'b',16,'z' from dual union all
    select 'b',17,'u' from dual union all
    select 'b',18,'v' from dual union all
    select 'b',19,'x' from dual union all
    select 'b',20,'x' from dual union all
    select 'c',21,'x' from dual union all
    select 'c',22,'y' from dual union all
    select 'c',23,'y' from dual union all
    select 'c',24,'y' from dual union all
    select 'c',25,'y' from dual union all
    select 'c',26,'x' from dual union all
    select 'c',27,'y' from dual union all
    select 'c',28,'z' from dual;
    col COl1    for a8
    col COl3 for a8
    col NewCol1 for a8
    col NewCol3 for a8
    select Col1,Col2,Col3,
    case when Rn <= count(distinct Col1) over()
    then max(Col1) over(order by Col1SumFlag
    range between Rn-Col1SumFlag following
    and Rn-Col1SumFlag following) end as NewCol1,
    case when Rn <= count(distinct Col3) over()
    then max(Col3) over(order by Col3SumFlag
    range between Rn-Col3SumFlag following
    and Rn-Col3SumFlag following) end as NewCol3
    from (select Col1,Col2,Col3,Rn,
    case Col1FirstFlag when 1 then sum(Col1FirstFlag) over(order by Col2)
    else 0 end as Col1SumFlag,
    case Col3FirstFlag when 1 then sum(Col3FirstFlag) over(order by Col2)
    else 0 end as Col3SumFlag
    from (select Col1,Col2,Col3,
    Row_Number() over(order by Col2) as Rn,
    case Row_Number() over(partition by Col1 order by Col2)
    when 1 then 1 else 0 end as Col1FirstFlag,
    case Row_Number() over(partition by Col3 order by Col2)
    when 1 then 1 else 0 end as Col3FirstFlag
    from ColTable))
    order by Col2;
    COL1  COL2  COL3  NEWCOL1   NEWCOL3
    ---- ---- ---- -------- --------
    a 1 x a x
    a 2 y b y
    a 3 z c z
    a 4 u null u
    b 5 v null v
    b 6 x null null
    b 7 x null null
    b 9 x null null
    b 10 y null null
    b 11 y null null
    b 12 y null null
    b 13 y null null
    b 14 x null null
    b 15 y null null
    b 16 z null null
    b 17 u null null
    b 18 v null null
    b 19 x null null
    b 20 x null null
    c 21 x null null
    c 22 y null null
    c 23 y null null
    c 24 y null null
    c 25 y null null
    c 26 x null null
    c 27 y null null
    c 28 z null null
    I used "Baaiwake" two times on case expression.
    http://aozoragakuen.sakura.ne.jp/houhou5/houhou52/node6.html
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    easy version
    create table Tumeru as
    select 'A' as Val,10 as SortKey from dual union
    select 'A', 20 from dual union
    select 'B', 30 from dual union
    select 'B', 40 from dual union
    select 'B', 50 from dual union
    select 'C', 60 from dual union
    select 'C', 70 from dual union
    select 'D', 80 from dual union
    select 'D', 90 from dual union
    select 'E',100 from dual union
    select 'E',110 from dual union
    select 'E',120 from dual union
    select 'E',130 from dual union
    select 'E',140 from dual union
    select 'A',150 from dual union
    select 'B',160 from dual union
    select 'C',170 from dual union
    select 'F',180 from dual union
    select 'G',190 from dual;
    col Val    for a8
    col NewVal for a8
    select Val,SortKey,
    case when Rn <= count(distinct Val) over()
    then max(Val) over(order by SumFlag
    range between Rn-SumFlag following
    and Rn-SumFlag following) end as NewVal
    from(select Val,SortKey,Rn,
    case FirstFlag when 1 then sum(FirstFlag) over(order by SortKey)
    else 0 end as SumFlag
    from (select Val,SortKey,
    Row_Number() over(order by SortKey) as Rn,
    case Row_Number() over(partition by Val order by SortKey)
    when 1 then 1 else 0 end as FirstFlag
    from Tumeru))
    order by SortKey;
    VAL  SORTKEY  NEWVAL
    --- ------- -------
    A 10 A
    A 20 B
    B 30 C
    B 40 D
    B 50 E
    C 60 F
    C 70 G
    D 80 null
    D 90 null
    E 100 null
    E 110 null
    E 120 null
    E 130 null
    E 140 null
    A 150 null
    B 160 null
    C 170 null
    F 180 null
    G 190 null
  • 572471
    572471 Member Posts: 984 Green Ribbon
    but the only drawback - you need to know the
    column, comprising the biggest amount of distinct
    values beforehand.

    No. There is no any difference in which column will
    be chosen as "based".
    For demo purposes I've added extra row number column
    to show full generality and symmetry of the
    approach
    :
    agreed, I was wrong
This discussion has been closed.