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:
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);
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!!!!!
You don't need the subquery that finds the maximum value.
Instead, ; you just need the first row after having ordered the rows:
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)
ORDER BY 4 DESC -- Added this line
) g
WHERE ROWNUM = 1. -- Added this line