How to get top score for individual records in Oracle?

Question

I have a table with a lot of records with duplicate fields. I want the most common of each of these duplicates.

So, if there is data in my table as shown below:

ID Field1 Field2 1 A 10 2 A 12 3 B 5 4 A 10 5 B 5 6 A 10 7 B 8 8 B 5 9 A 10 

I can choose a separate one and get the quantity:

 select distinct Field1, Field2, count(Field1) from Table group by Field1, Field2 order by Field1, count(Field1) desc 

And it will give me

 Field1 Field2 Count A 10 4 A 12 1 B 5 3 B 8 1 

However, I only need the entries for each field 1 that have the highest score. I fought rank () over sections and subqueries, but did not find the correct syntax for using two fields for uniqueness and selecting the top record in a row. I searched, and I’m sure it was asked, but I can’t find it.

I want to get the following

 Field1 Field2 (optional) Count A 10 4 B 5 3 

The goal is to look at a table with a small amount of incorrect data (the relationship between field 1 and field 2) and determine what it MUST be based on what it usually represents. I don’t know how many bad entries there are, so removing Count below a certain threshold will work, but it seems a bit shabby.

If this is better, I can create a temp table to insert my various values ​​and then select from there, but it doesn't seem like it is necessary.

Answer 1

I think this is what you are looking for:

 select field1, field2, cnt from (select field1, field2, cnt, rank() over (partition by field1 order by cnt desc) rnk from (select distinct Field1, Field2, count(Field1) cnt from Table1 group by Field1, Field2 order by Field1, count(Field1) desc) ) where rnk = 1; 

SQL Fiddle: http://sqlfiddle.com/#!4/fe96d/3

Answer 2

This is a bit inefficient due to several layers of nested subqueries. However, it should be quite effective. And it should be easy enough to follow the steps in SQL

 SQL> ed Wrote file afiedt.buf 1 with x as ( 2 select 1 id, 'A' field1, 10 field2 from dual union all 3 select 2, 'A', 12 from dual union all 4 select 3, 'B', 5 from dual union all 5 select 4, 'A', 10 from dual union all 6 select 5, 'B', 5 from dual union all 7 select 6, 'A', 10 from dual union all 8 select 7, 'B', 8 from dual union all 9 select 8, 'B', 5 from dual union all 10 select 9, 'A', 10 from dual 11 ) 12 select field1, 13 field2, 14 cnt 15 from (select field1, 16 field2, 17 cnt, 18 rank() over (partition by field1 19 order by cnt desc) rnk 20 from (select field1, field2, count(*) cnt 21 from x 22 group by field1, field2)) 23* where rnk = 1 SQL> / F FIELD2 CNT - ---------- ---------- A 10 4 B 5 3 

Answer 3

And the third approach;)

 select field1, field2, max_cnt from ( select field1, field2, cnt, max(cnt) over (partition by field1, field2) as max_cnt, row_number() over (partition by field1 order by cnt desc) as rn from ( select field1, field2, count(*) over (partition by Field1, Field2) as cnt from idlist ) t1 ) t2 where max_cnt = cnt and rn = 1 

SQLFiddle: http://sqlfiddle.com/#!4/8461f/1