rajprakashraj Posted April 27, 2021 Report Share Posted April 27, 2021 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 Quote Link to comment Share on other sites More sharing options...
ponugupati Posted April 27, 2021 Report Share Posted April 27, 2021 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 Quote Link to comment Share on other sites More sharing options...
rajprakashraj Posted April 27, 2021 Author Report Share Posted April 27, 2021 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 Quote Link to comment Share on other sites More sharing options...
ponugupati Posted April 27, 2021 Report Share Posted April 27, 2021 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 Quote Link to comment Share on other sites More sharing options...
Joker_007 Posted April 27, 2021 Report Share Posted April 27, 2021 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... Quote Link to comment Share on other sites More sharing options...
bommali Posted April 28, 2021 Report Share Posted April 28, 2021 https://www.sqlshack.com/sql-partition-by-clause-overview/ Try partition by with row number() clause Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.