I would like to retrieve the record that has the maximum number of counts (records) in the table. I have written a query using the MAX(COUNT(*)) but it doesn't work in SQL Server.
select count(*) total, a.fd_acct_account_no from payment_details_temp a group by a.fd_acct_account_no having count(*) = (select max(count(*)) from payment_details_temp b group by b.fd_acct_account_no )
Appreciate your support.
Since this is SQL Server, we can utilize the handy-dandy TOP keyword, which isn't standard SQL, but which serves the purpose very nicely:
select top 1 count(*) total , fd_acct_account_no from payment_details_temp group by fd_acct_account_no order by count(*) desc
This was first published in September 2005