title: How to get a top count for distinct records in Oracle?
body: <p>I have a table with a lot of records with some fields duplicated.  I want the most common of each of those duplications.</p>
  
  <p>So, if my table has data like below:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>I can select distinct and get counts: </p>
  
  <pre><code>select distinct Field1, Field2, count(Field1)
  from Table
  group by Field1, Field2
  order by Field1, count(Field1) desc
  </code></pre>
  
  <p>And that will give me</p>
  
  <pre><code>Field1    Field2     Count
  A         10         4
  A         12         1
  B          5         3
  B          8         1
  </code></pre>
  
  <p>However, I only want the records for each Field1 that have the highest count.  I've been fighting with rank() over partition and subqueries, but haven't found the correct syntax for using two fields for uniqueness and selecting the top record by count.  I've been searching, and I'm sure this has been asked, but I can't find it.  </p>
  
  <p>I want to get the following</p>
  
  <pre><code>Field1     Field2       (optional) Count 
   A          10           4
   B           5           3
  </code></pre>
  
  <p>The goal is to look at a table that has just a little bit of incorrect data (linking between field1 and field2 wrong) and determine what it SHOULD be based on what it usually is.  I don't know how many bad records there are, so eliminating Count below a certain threshold would work, but seems a bit kludgy.</p>
  
  <p>If it is better, I can make a temp table to put my distinct values into and then select from there, but it doesn't seem like that should be necessary.</p>