oracle sql doubt

[rajprakashraj]

table t lo below columns unnay

doc_num	doc_ref_num	person_id	person_name	person_type
1	1	1	tom	PR
1	1	2	john	JT
1	2	2	john	PR
1	2	1	tom	JT
1	3	3	chris	PR
1	3	4	jim	JT
1	4	5	james	PR
2	1	1	tom	PR
2	1	2	john	JT
2	2	2	john	JT
2	2	1	tom	PR
3	1	1	tom	PR
3	1	2	john	JT
3	2	4	jim	JT
3	2	5	james	PR

 

i want to get doc_num where person id is different between doc_ref_num under same doc_num. person_type PR,JT flip ina for same persons i don't want them. so my result should return only doc_num 1,3 as doc_num 2 have same person_id but they just flipped person_type for different doc_ref_num.

output should be:

doc_num	doc_ref_num	person_id	person_name	person_type
1	1	1	tom	PR
1	1	2	john	JT
1	2	2	john	PR
1	2	1	tom	JT
1	3	3	chris	PR
1	3	4	jim	JT
1	4	5	james	PR
3	1	1	tom	PR
3	1	2	john	JT
3	2	4	jim	JT
3	2	5	james	PR

 

 

 

[ponugupati]

Hi Raj,

 

Not sure if this is what you are looking for:

 

select *

from (select t.* , count(distinct person_name||person_type) over (partition by doc_num,person_id) cnt

from t

)

where cnt > 1

[rajprakashraj]

14 minutes ago, ponugupati said:

Hi Raj,

 

Not sure if this is what you are looking for:

 

select *

from (select t.* , count(distinct person_name||person_type) over (partition by doc_num,person_id) cnt

from t

)

where cnt > 1

almost close undi dude but below scenario unte, it will show up in ur query but it should not

doc_num	doc_ref_num	person_id	person_name	person_type
5	1	1	tom	PR
5	1	2	john	JT
5	2	2	john	PR

 

[ponugupati]

You dont want to check on person_type then use this query :

 

select *

from (select t.* , count(distinct person_name) over (partition by doc_num,person_id) cnt

from t

)

where cnt > 1

[Joker_007]

10 hours ago, rajprakashraj said:

table t lo below columns unnay

doc_num	doc_ref_num	person_id	person_name	person_type
1	1	1	tom	PR
1	1	2	john	JT
1	2	2	john	PR
1	2	1	tom	JT
1	3	3	chris	PR
1	3	4	jim	JT
1	4	5	james	PR
2	1	1	tom	PR
2	1	2	john	JT
2	2	2	john	JT
2	2	1	tom	PR
3	1	1	tom	PR
3	1	2	john	JT
3	2	4	jim	JT
3	2	5	james	PR

 

i want to get doc_num where person id is different between doc_ref_num under same doc_num. person_type PR,JT flip ina for same persons i don't want them. so my result should return only doc_num 1,3 as doc_num 2 have same person_id but they just flipped person_type for different doc_ref_num.

output should be:

doc_num	doc_ref_num	person_id	person_name	person_type
1	1	1	tom	PR
1	1	2	john	JT
1	2	2	john	PR
1	2	1	tom	JT
1	3	3	chris	PR
1	3	4	jim	JT
1	4	5	james	PR
3	1	1	tom	PR
3	1	2	john	JT
3	2	4	jim	JT
3	2	5	james	PR

 

 

 

baboi.. ee code naa artham kaavatledu...

 

[bommali]

https://www.sqlshack.com/sql-partition-by-clause-overview/

 

Try partition by with row number() clause