Forum Stats

  • 3,741,232 Users
  • 2,248,397 Discussions
  • 7,861,693 Comments

Discussions

ignore X in group by

474007
474007 Member Posts: 317
edited Nov 5, 2010 9:02PM in SQL & PL/SQL
Hi!

How can i get these results:
ID	VAL
1 A
2 B
3 "a string"
4 C
5 "a string"
6 "a string"
7 "a string"
8 G

from:
ID VAL
1 A
1 x
1 A
1 x
1 A
1 A
1 A
2 B
2 B
2 B
2 x
2 B
2 x
3 A
3 A
3 K
3 A
3 A
3 A
4 C
4 C
4 x
4 C
4 C
4 x
4 C
5 D
5 B
5 D
5 D
5 D
6 E
6 F
6 E
6 M
6 E
7 L
7 F
7 F
8 G
8 G
8 G
8 G
8 G
8 G
Thanks in advance.

Comments

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    WHERE val <> 'x'

    But I don't get your "a string" thing.
  • 474007
    474007 Member Posts: 317
    The problem is that i only want the group by val having val <> 'y' on those. Where there are different values (exclude x) on the same id then there can not be a group by. See output example below:

    ID VAL
    1 A
    2 B
    3 ZZZ
    4 C
    5 ZZZ
    6 ZZZ
    7 ZZZ
    8 G
  • 474007
    474007 Member Posts: 317
    The problem is that i only want the group by val having val <> 'x' on those. Where there are different values (exclude x) on the same id then there can not be a group by. See output example below:

    ID VAL
    1 A
    2 B
    3 ZZZ
    4 C
    5 ZZZ
    6 ZZZ
    7 ZZZ
    8 G
  • John Spencer
    John Spencer Member Posts: 8,567
    If I understand correctly, you want to exclude all records with val = 'x'. From the remaining records, if all vals for an id are the same then return the value of val, otherwise return 'a string'

    If that is correct, then this is one way:
    SQL> SELECT DISTINCT id, DECODE(val_cnt, 1, val, 'a string') val
    2 FROM (SELECT id, val, COUNT(DISTINCT val) OVER (PARTITION BY id) val_cnt
    3 FROM t
    4 WHERE val <> 'x');

    ID VAL
    ---------- --------
    1 A
    2 B
    3 a string
    4 C
    5 a string
    6 a string
    7 a string
    8 G
    HTH
    John
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 5, 2010 9:02PM
    SELECT distinct ID,
    case when COUNT(DISTINCT VAL) OVER (PARTITION BY id) > 1
         then 'a string' else VAL end as VAL
    FROM (select 1 as ID,'A' as VAL from dual
    union select 1,'x' from dual
    union select 1,'A' from dual
    union select 1,'x' from dual
    union select 1,'A' from dual
    union select 1,'A' from dual
    union select 1,'A' from dual
    union select 2,'B' from dual
    union select 2,'B' from dual
    union select 2,'B' from dual
    union select 2,'x' from dual
    union select 2,'B' from dual
    union select 2,'x' from dual
    union select 3,'A' from dual
    union select 3,'A' from dual
    union select 3,'K' from dual
    union select 3,'A' from dual
    union select 3,'A' from dual
    union select 3,'A' from dual
    union select 4,'C' from dual
    union select 4,'C' from dual
    union select 4,'x' from dual
    union select 4,'C' from dual
    union select 4,'C' from dual
    union select 4,'x' from dual
    union select 4,'C' from dual
    union select 5,'D' from dual
    union select 5,'B' from dual
    union select 5,'D' from dual
    union select 5,'D' from dual
    union select 5,'D' from dual
    union select 6,'E' from dual
    union select 6,'F' from dual
    union select 6,'E' from dual
    union select 6,'M' from dual
    union select 6,'E' from dual
    union select 7,'L' from dual
    union select 7,'F' from dual
    union select 7,'F' from dual
    union select 8,'G' from dual
    union select 8,'G' from dual
    union select 8,'G' from dual
    union select 8,'G' from dual
    union select 8,'G' from dual
    union select 8,'G' from dual)
    WHERE val <> 'x';
    OracleSQLPuzzle
    http://www.geocities.jp/oraclesqlpuzzle
  • 474007
    474007 Member Posts: 317
    Thanks John,

    This is exactly what i need. Can u explain why 1 in the search of the DECODE leads to the expression of the value of val while the results of val_cnt are:

    id ----val----- COUNT(DISTINCT val)
    1----- A ----- 1
    1----- A ----- 1
    1----- A ----- 1
    1----- A ----- 1
    1----- A ----- 1
    2----- B ----- 1
    2----- B ----- 1
    2----- B ----- 1
    2----- B ----- 1
    and so on...
  • 474007
    474007 Member Posts: 317
    Hi aketi,

    Your query will not work for me because of the dynamic id's.

    Greetings
  • BluShadow
    BluShadow Member, Moderator Posts: 41,088 Red Diamond
    Your query will not work for me because of the
    dynamic id's.
    I think aketi's solution works fine...
      1  SELECT distinct ID,
      2  case when COUNT(DISTINCT VAL) OVER (PARTITION BY id) > 1
      3  then 'a string' else VAL end as VAL
      4  FROM t
      5  WHERE val <> 'x'
      6* order by id
    SQL> /
    
            ID VAL
    ---------- --------
             1 A
             2 B
             3 a string
             4 C
             5 a string
             6 a string
             7 a string
             8 G
    
    8 rows selected.
    
    SQL>
    What do you mean by dynamic id's?
  • 474007
    474007 Member Posts: 317
    Without the UNION SELECT it works indeed. I meant with dynamic id's that i don't know wich id's i can expect.
  • BluShadow
    BluShadow Member, Moderator Posts: 41,088 Red Diamond
    He's only used the union select to prevent having to create a table on his database with all the data in. Another common method to achieve the same would be something like:-
    WITH t AS
      (select 'fred' as name from dual union
       select 'bob' from dual union
       select 'jim' from dual)
    SELECT name
    FROM t;
    which is the same as:-
    SELECT name
    FROM (select 'fred' as name from dual union
       select 'bob' from dual union
       select 'jim' from dual)
    which is the same as:-
    CREATE TABLE t AS  select 'fred' as name from dual union
       select 'bob' from dual union
       select 'jim' from dual;
    
    SELECT name FROM t;
    apart from this latter one involves actually creating a table which will need dropping again later. The other's are like virtual tables that only exist for the duration of the query.

    ;)
  • John Spencer
    John Spencer Member Posts: 8,567
    Aketi's solution is exactly equivalent to mine, and a little simpler. I don't think that there would be any performance differences between the two, it is mostly a matter of personal style.

    DECODE is essentially an immediate if (IIF) type function. The syntax for DECODE is:
    DECODE(exprssion_to_evaluate, Value_to_compare1, result_if_match1,
    Value_to_compare2, result_if_match2,
    . . .,
    result_if_nomatch)
    So, in my query, it evaluates val_cnt and compares it to 1. If they are the same, then it returns val, if they are different, it returns 'a string'. I could have compared val_cnt to multiple values and returned a different string for each. Something like:
    SQL> SELECT DISTINCT id, DECODE(val_cnt, 1, 'One Val',
    2 2, 'Two Vals',
    3 'Over Two') val
    4 FROM (SELECT id, val, COUNT(DISTINCT val) OVER (PARTITION BY id) val_cnt
    5 FROM t
    6 WHERE val <> 'x');

    ID VAL
    ---------- --------
    1 One Val
    2 One Val
    3 Two Vals
    4 One Val
    5 Two Vals
    6 Over Two
    7 Two Vals
    8 One Val
    HTH
    John
  • 474007
    474007 Member Posts: 317
    Thank u all for the variants, they are very helpfull. I thought it would be more difficult but now i see this i was wondering if the following is also possible?
    ID	VAL	VAR
    1 A
    2 B
    3 var 5xA,1xK
    4 C
    5 var 4xD,1xB
    6 var 3xE,1xF,1xM
    7 var 1xL,2xF
    8 G
  • 474007
    474007 Member Posts: 317
    I made a mistake in making the example. I have used the concept (see below) but found out that i forgot a field (CODE). Can it be done in the same PARTION BY or do i need another inline_view?
    i want to query:
    CODE ID VAL VAR
    1 2 B
    1 3 var 5xA,1xK
    1 4 C
    1 5 var 4xD,1xB
    1 6 var 3xE,1xF,1xM
    1 7 var 1xL,2xF
    1 8 G
    2 2 T 3xT,1xZ
    2 3 var 5xR,1xK
    2 4 C
    2 5 var 4xW,1xT
    2 6 P
    2 7 var 1xL,2xF
    2 8 G

    from:
    CODE ID VAL
    1 1 A
    1 1 x
    1 1 A
    1 1 x
    1 1 A
    1 1 A
    1 1 A
    1 2 B
    1 2 B
    1 2 B
    1 2 x
    1 2 B
    1 2 x
    1 3 A
    1 3 A
    1 3 K
    1 3 A
    1 3 A
    1 3 A
    1 4 C
    1 4 C
    1 4 x
    1 4 C
    1 4 C
    1 4 x
    1 4 C
    1 5 D
    1 5 B
    1 5 D
    1 5 D
    1 5 D
    1 6 E
    1 6 F
    1 6 E
    1 6 M
    1 6 E
    1 7 L
    1 7 F
    1 7 F
    1 8 G
    1 8 G
    1 8 G
    1 8 G
    1 8 G
    1 8 G
    2 1 R
    2 1 x
    2 1 R
    2 1 x
    2 1 R
    2 1 R
    2 1 R
    2 2 T
    2 2 Z
    2 2 T
    2 2 x
    2 2 T
    2 2 x
    2 3 R
    2 3 R
    2 3 K
    2 3 R
    2 3 R
    2 3 R
    2 4 C
    2 4 C
    2 4 x
    2 4 C
    2 4 C
    2 4 x
    2 4 C
    2 5 W
    2 5 T
    2 5 W
    2 5 W
    2 5 W
    2 6 P
    2 6 P
    2 6 P
    2 6 P
    2 6 P
    2 7 L
    2 7 F
    2 7 F
    2 8 G
    2 8 G
    2 8 G
    2 8 G
    2 8 G
    2 8 G
    and so on…


    practical usage:
    SELECT inline_view.sdg_id || '_' || inline_view.sample_id || '_' || inline_view.test_template_id AS Reference,
    inline_view.u_plot_number AS Research_Group,
    inline_view.external_reference AS Parental_Line,
    inline_view.u_box_code AS Box,
    inline_view.description AS Test,
    CASE WHEN COUNT(DISTINCT NVL(inline_view_finalresult.u_finalresult_user.u_overruled_result,inline_view_finalresult.u_finalresult_user.u_calculated_result))
    OVER (PARTITION BY inline_view_finalresult.u_test_template_id) > 1
    THEN 'spl'
    ELSE NVL(inline_view_finalresult.u_finalresult_user.u_overruled_result,inline_view_finalresult.u_finalresult_user.u_calculated_result)
    END AS Result
    FROM ( SELECT DISTINCT sdg.sdg_id,
    sample.sample_id,
    test_template.test_template_id,
    sample_user.u_plot_number,
    sdg.external_reference,
    sample_user.u_box_code,
    test.description
    FROM sdg,sdg_user,sample,sample_user,aliquot,test,test_template
    WHERE sdg.sdg_id = sdg_user.sdg_id
    AND sdg.sdg_id = sample.sdg_id
    AND sample.sample_id = sample_user.sample_id
    AND aliquot.sample_id = sample.sample_id
    AND test.aliquot_id = aliquot.aliquot_id
    AND test_template.test_template_id = test.test_template_id
    AND sdg.sdg_id =:SDGID
    ) inline_view,
    ( SELECT u_finalresult_user.u_calculated_result,
    u_finalresult_user.u_overruled_result,
    u_finalresult_user.u_sample_id,
    u_finalresult_user.u_test_template_id
    FROM u_finalresult_user
    WHERE u_finalresult_user.u_requested = 'T'
    AND NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result) <> 'X'
    ) inline_view_finalresult
    WHERE inline_view.sample_id = inline_view_finalresult.u_sample_id (+)
    AND inline_view.test_template_id = inline_view_finalresult.u_test_template_id (+)
  • 474007
    474007 Member Posts: 317
    I have found a solution for the extra field CODE but i still need help with the VAR column. How can i accomplish tihs?
    SELECT DISTINCT inline_view.u_plot_number AS Research_Group,
    inline_view.external_reference AS Parental_Line,
    inline_view.u_box_code AS Box,
    inline_view.description AS Test,
    CASE WHEN COUNT(DISTINCT NVL(inline_view_finalresult.u_overruled_result,inline_view_finalresult.u_calculated_result))
    OVER (PARTITION BY NVL(inline_view_finalresult.u_sdg_id,inline_view_finalresult.u_test_template_id)) > 1
    THEN 'spl'
    ELSE NVL(inline_view_finalresult.u_overruled_result,inline_view_finalresult.u_calculated_result)
    END AS Result
    FROM ( SELECT DISTINCT sdg.sdg_id,
    sample.sample_id,
    test_template.test_template_id,
    sample_user.u_plot_number,
    sdg.external_reference,
    sample_user.u_box_code,
    test.description
    FROM sdg,sdg_user,sample,sample_user,aliquot,test,test_template
    WHERE sdg.sdg_id = sdg_user.sdg_id
    AND sdg.sdg_id = sample.sdg_id
    AND sample.sample_id = sample_user.sample_id
    AND aliquot.sample_id = sample.sample_id
    AND test.aliquot_id = aliquot.aliquot_id
    AND test_template.test_template_id = test.test_template_id
    AND sdg.sdg_id >505
    ) inline_view,
    ( SELECT u_finalresult_user.u_sdg_id,
    u_finalresult_user.u_calculated_result,
    u_finalresult_user.u_overruled_result,
    u_finalresult_user.u_sample_id,
    u_finalresult_user.u_test_template_id
    FROM u_finalresult_user
    WHERE u_finalresult_user.u_requested = 'T'
    AND NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result) <> 'X'
    ) inline_view_finalresult
    WHERE inline_view.sample_id = inline_view_finalresult.u_sample_id (+)
    AND inline_view.test_template_id = inline_view_finalresult.u_test_template_id (+)
    ORDER BY inline_view.description,inline_view.u_box_code
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 5, 2010 9:01PM
    create Table TestTable as
    select 1 as CODE,1 as ID,'A' as VAL from dual
    union all select 1,1,'x' from dual
    union all select 1,1,'A' from dual
    union all select 1,1,'x' from dual
    union all select 1,1,'A' from dual
    union all select 1,1,'A' from dual
    union all select 1,1,'A' from dual
    union all select 1,2,'B' from dual
    union all select 1,2,'B' from dual
    union all select 1,2,'B' from dual
    union all select 1,2,'x' from dual
    union all select 1,2,'B' from dual
    union all select 1,2,'x' from dual
    union all select 1,3,'A' from dual
    union all select 1,3,'A' from dual
    union all select 1,3,'K' from dual
    union all select 1,3,'A' from dual
    union all select 1,3,'A' from dual
    union all select 1,3,'A' from dual
    union all select 1,4,'C' from dual
    union all select 1,4,'C' from dual
    union all select 1,4,'x' from dual
    union all select 1,4,'C' from dual
    union all select 1,4,'C' from dual
    union all select 1,4,'x' from dual
    union all select 1,4,'C' from dual
    union all select 1,5,'D' from dual
    union all select 1,5,'B' from dual
    union all select 1,5,'D' from dual
    union all select 1,5,'D' from dual
    union all select 1,5,'D' from dual
    union all select 1,6,'E' from dual
    union all select 1,6,'F' from dual
    union all select 1,6,'E' from dual
    union all select 1,6,'M' from dual
    union all select 1,6,'E' from dual
    union all select 1,7,'L' from dual
    union all select 1,7,'F' from dual
    union all select 1,7,'F' from dual
    union all select 1,8,'G' from dual
    union all select 1,8,'G' from dual
    union all select 1,8,'G' from dual
    union all select 1,8,'G' from dual
    union all select 1,8,'G' from dual
    union all select 1,8,'G' from dual
    union all select 2,1,'R' from dual
    union all select 2,1,'x' from dual
    union all select 2,1,'R' from dual
    union all select 2,1,'x' from dual
    union all select 2,1,'R' from dual
    union all select 2,1,'R' from dual
    union all select 2,1,'R' from dual
    union all select 2,2,'T' from dual
    union all select 2,2,'Z' from dual
    union all select 2,2,'T' from dual
    union all select 2,2,'x' from dual
    union all select 2,2,'T' from dual
    union all select 2,2,'x' from dual
    union all select 2,3,'R' from dual
    union all select 2,3,'R' from dual
    union all select 2,3,'K' from dual
    union all select 2,3,'R' from dual
    union all select 2,3,'R' from dual
    union all select 2,3,'R' from dual
    union all select 2,4,'C' from dual
    union all select 2,4,'C' from dual
    union all select 2,4,'x' from dual
    union all select 2,4,'C' from dual
    union all select 2,4,'C' from dual
    union all select 2,4,'x' from dual
    union all select 2,4,'C' from dual
    union all select 2,5,'W' from dual
    union all select 2,5,'T' from dual
    union all select 2,5,'W' from dual
    union all select 2,5,'W' from dual
    union all select 2,5,'W' from dual
    union all select 2,6,'P' from dual
    union all select 2,6,'P' from dual
    union all select 2,6,'P' from dual
    union all select 2,6,'P' from dual
    union all select 2,6,'P' from dual
    union all select 2,7,'L' from dual
    union all select 2,7,'F' from dual
    union all select 2,7,'F' from dual
    union all select 2,8,'G' from dual
    union all select 2,8,'G' from dual
    union all select 2,8,'G' from dual
    union all select 2,8,'G' from dual
    union all select 2,8,'G' from dual
    union all select 2,8,'G' from dual;
    col Code for 9999
    col ID for 99
    col Val for a4
    col Var for a20
    
    Oracle10g(Connect_by_IsLeaf)
    
    select Code,ID,
    decode(Level,1,Val,'Var') as Val,
    case when Level >= 2
         then SubStr(sys_connect_by_path(ValCount,','),2) end as Var
    from (select Code,ID,Val,
          to_char(count(*)) || 'x' || Val as ValCount,
          Row_Number() over(partition by Code,ID order by count(*) desc,Val) as Rank
            from TestTable
           where Val != 'x'
          group by Code,ID,Val)
    where Connect_by_IsLeaf = 1
    Start With Rank = 1
    connect by prior Code = Code
           and prior ID   = ID
           and prior Rank = Rank-1
    order by Code,ID;
    
    Oracle9i
    
    select Code,ID,
    decode(Level,1,Val,'Var') as Val,
    case when Level >= 2
         then SubStr(sys_connect_by_path(ValCount,','),2) end as Var
    from (select Code,ID,Val,
          to_char(count(*)) || 'x' || Val as ValCount,
          Row_Number() over(partition by Code,ID order by count(*) desc,Val) as Rank,
          count(*) over(partition by Code,ID) as MaxLevel
            from TestTable
           where Val != 'x'
          group by Code,ID,Val)
    where Level = MaxLevel
    Start With Rank = 1
    connect by prior Code = Code
           and prior ID   = ID
           and prior Rank = Rank-1
    order by Code,ID;
    
     CODE   ID  VAL   VAR
    -----  ---  ----  -----------
        1    1  A     null
        1    2  B     null
        1    3  Var   5xA,1xK
        1    4  C     null
        1    5  Var   4xD,1xB
        1    6  Var   3xE,1xF,1xM
        1    7  Var   2xF,1xL
        1    8  G     null
        2    1  R     null
        2    2  Var   3xT,1xZ
        2    3  Var   5xR,1xK
        2    4  C     null
        2    5  Var   4xW,1xT
        2    6  P     null
        2    7  Var   2xF,1xL
        2    8  G     null
    1113377

    OracleSQLPuzzle
    http://www.geocities.jp/oraclesqlpuzzle
  • 474007
    474007 Member Posts: 317
    Hi Aketi,

    Your Oracle9i example is usefull. Also the first link you posted gave me an understanding how it can works. In my situation however it is very difficult how it can be done with a good structure of the query. I think that because of the "group by" in my situation causes the performance problem (see link below). Will you take a look at my query? Maybe you can see what is wrong with it.

    Thanks in advance.

    380361
This discussion has been closed.