 3,741,232 Users
 2,248,397 Discussions
 7,861,693 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
 381.2K All Categories
 2.1K Data
 204 Big Data Appliance
 1.9K Data Science
 446.6K Databases
 220.6K General Database Discussions
 3.7K Java and JavaScript in the Database
 23 Multilingual Engine
 511 MySQL Community Space
 462 NoSQL Database
 7.7K Oracle Database Express Edition (XE)
 2.8K ORDS, SODA & JSON in the Database
 448 SQLcl
 3.9K SQL Developer Data Modeler
 185.6K SQL & PL/SQL
 20.8K SQL Developer
 291.8K Development
 7 Developer Projects
 117 Programming Languages
 288.6K Development Tools
 94 DevOps
 3K QA/Testing
 645.3K Java
 18 Java Learning Subscription
 36.9K Database Connectivity
 150 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.7K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 12 Java Essentials
 141 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 197 Java User Groups
 23 JavaScript  Nashorn
 Programs
 192 LiveLabs
 34 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.7K Other Languages
 2.3K Chinese
 165 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
ignore X in group by
474007
Member Posts: 317
Hi!
How can i get these results:
How can i get these results:
ID VALThanks in advance.
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
Comments

WHERE val <> 'x'
But I don't get your "a string" thing. 
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 
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 
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
HTH
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
John 
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 
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... 
Hi aketi,
Your query will not work for me because of the dynamic id's.
Greetings 
Your query will not work for me because of theI think aketi's solution works fine...
dynamic id's.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? 
Without the UNION SELECT it works indeed. I meant with dynamic id's that i don't know wich id's i can expect.

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.

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,
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:
Value_to_compare2, result_if_match2,
. . .,
result_if_nomatch)SQL> SELECT DISTINCT id, DECODE(val_cnt, 1, 'One Val',
HTH
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
John 
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 
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 (+) 
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 
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 = Rank1 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 = Rank1 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 
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.