I would like to retrieve the record that has the maximum number of counts (records) in the table. I have written...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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 about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our 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.