Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ignore X in group by

474007Apr 5 2006 — edited Nov 5 2010

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
WHERE val <> 'x'

But I don't get your "a string" thing.
474007
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
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

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 
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
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
Hi aketi,

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

Greetings
BluShadow
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
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
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

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

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

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

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
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
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
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 8 2006
Added on Apr 5 2006
16 comments
10,701 views