SQL show the max of count

Question

I am using SQL server 2014. I have two tables Member and MemberPosition. The MemberPostion table records a history of each member in the database. The memberID is the primary key in the member table, and is a foreign key in the MemberPosition table. I need to return the member that has held the most positions. Here is the query I am using:

Select 
M.MemberFName + ' ' + M.MemberLName as Name

from Member M

Join MemberPosition MP
ON MP.MemberID = M.MemberID

Group By M.MemberFName + ' ' + M.MemberLName

Having Count(MP.MemberID) = (Select max(P) from
                                    (Select Count(MP.MemberID) as P From MP))

I am getting an error near the last ) It tells me incorrect syntax near 'End of file'. Expecting AS,ID, or QUOTED_ID.

Can you tell me what I am doing wrong. I have tried following other examples from the forums but I cannot figure this out.

Answer 1

Subqueries in the from clause need an alias. In your case, this is buried deep in the having clause:

Select M.MemberFName + ' ' + M.MemberLName as Name
from Member M Join
     MemberPosition MP
     ON MP.MemberID = M.MemberID
Group By M.MemberFName + ' ' + M.MemberLName
Having Count(MP.MemberID) = (Select max(P)
                             from (Select Count(MP.MemberID) as P
                                   From MP
                                  ) p
------------------------------------^
                            );

There are other ways to do what you want but this follows your query.

Here is an alternative:

Select TOP 1 WITH TIES M.MemberFName + ' ' + M.MemberLName as Name
from Member M Join
     MemberPosition MP
     ON MP.MemberID = M.MemberID
Group By M.MemberFName + ' ' + M.MemberLName
ORDER BY COUNT(*) DESC;