title: What is the best way to select rows with maximum value?
body: <p>I have come across a task, I managed to complete the objective but the solution I got is not optimum, I need more optimum solution. I have used normal Sub Queries May be Correlated Sub Query can solve this better. </p>
  
  <p>This is the table i made </p>
  
  <pre><code>SELECT custid, 
         count(DISTINCT bid) AS Total 
  FROM   loan 
  GROUP  BY custid; 
  </code></pre>
  
  <p>The output of this is like:- </p>
  
  <p><img src="https://i.stack.imgur.com/Rae36.png" alt="enter image description here"></p>
  
  <p>What I want is the <code>custid</code> having maximum Total. </p>
  
  <p>One way to do it is using <code>Order by Total DESC LIMIT 1</code> but this will give only 1 result. </p>
  
  <p>What I did is </p>
  
  <pre><code>SELECT custid 
  FROM   (SELECT custid, 
                 count(DISTINCT bid) AS Total 
          FROM   loan 
          GROUP  BY custid) c1 
  WHERE  total = (SELECT max(Total) 
                  FROM   (SELECT custid, 
                                 count(DISTINCT bid) AS Total 
                          FROM   loan 
                          GROUP  BY custid) c2) 
  </code></pre>
  
  <p>This gives me correct result that is </p>
  
  <p><img src="https://i.stack.imgur.com/43WvA.png" alt="enter image description here"></p>
  
  <p>What I want to do is reduce the code, because here I am writing the same thing again. I know there must be a simpler way to do it. Maybe a correlated query. </p>
  
  <p>Looking for some good answers. This is basically to clear my concepts only </p>
  
  <p>Sorry, if it is noob question. I am a noob to SQL.</p>