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, 
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.

    Requires Free Membership to View

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  
    by fd_acct_account_no
    by count(*) desc

Simple, yes?

This was first published in September 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: