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 an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.