## Forum Stats

• 3,741,232 Users
• 2,248,397 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# ignore X in group by

Member Posts: 317
edited Nov 5, 2010 9:02PM
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

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

But I don't get your "a string" thing.
• 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
• 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
• 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
• 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
• 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...
• Member Posts: 317
Hi aketi,

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

Greetings
• 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?
• 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.
• 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.

• 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:
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
• 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
• 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 (+)
• 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
• 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
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
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
• 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.