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
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading