Optimal Oracle SQL Query to complete group work across multiple columns in a separate table containing ~ 7,000,000 records

Question

I am a beginner SQL in need of some advice. What is the most efficient (quick request) way to do the following -

Select all columns from the table after -

-Use "Group by" based on the unique values ​​contained in two columns: "top_line_id" and "external_reference".

-Selection of one record from each group based on the maximum or minimum value (no matter which one) is contained in another field, such as support_id.

Someone from my team has provided a request below, but I cannot get it to work. I get an error "invalid relational operator" when I try to execute it.

Select * from STAGE.SFS_GH_R3_IB_ENTLMNT_CONTACTS Where support_id, external_reference, top_line_id in ( select max(support_id), external_reference, top_line_id from STAGE.SFS_GH_R3_IB_ENTLMNT_CONTACTS ) 

One more thing - the columns in which we execute the By group contain null values ​​in some records. We would like to exclude from the request.

Any help you can provide is greatly appreciated.

Answer 1

Although you express this as a group on demand, there is another approach using row_number (). This lists each row in the group based on the "order by" clause. In the following query, it lists each group based on external_reference and top_line_id, sorted by support_id:

 select * from (Select t.*, row_number() over (partition by external_reference, top_line_id order by support_id) as seqnum from STAGE.SFS_GH_R3_IB_ENTLMNT_CONTACTS t ) where seqnum = 1 

Answer 2

This should work (can't check it)

 SELECT * FROM stage.sfs_gh_r3_ib_entlmnt_contacts WHERE (support_id, external_reference, top_line_id) IN ( SELECT max(support_id), external_reference, top_line_id FROM stage.sfs_gh_r3_ib_entlmnt_contacts WHERE external_reference IS NOT NULL AND top_line_id IS NOT NULL GROUP BY top_line_id, external_reference )