title: How to use MAX() on a subquery result?
body: <p>I am new to Oracle and the SQL world. I have a slight issue with a query that I cannot figure out for the life of me, I have spent a few hours trying different approaches and I cannot get the result I expect. So heres my query:</p>
  
  <pre><code>SELECT *
  from(Select membership.mem_desc,membership.mem_max_rentals,membership_history.mem_type,      
      count(membership_history.MEM_TYPE) as membership_count
      from membership_history
      JOIN membership ON membership.mem_type = membership_history.mem_type
      group by (membership_history.mem_type,membership.mem_desc,membership.mem_max_rentals)
      ) g
  WHERE g.membership_count = (select MAX(membership_count) from g); 
  </code></pre>
  
  <p>So the inner query works perfectly and returns  two results. Now that I have these two values I am trying to figure out how to return the row with the maximum value of membership_count which Is where I keep getting stuck. In the above query I tried using the MAX() in the where clause but inside that select I keep getting the error 'table not found'(meaning 'g'). So my question is how do I use the MAX() function on the results of my subquery? Any thoughts or suggestions would be greatly appreciated!!!!!</p>