Sql select most repeated value

Question

Hi,

I have a database called a “reader”, and a table called "dailyreader". The dailyreader has 6 columns and i am looking to create a query to select the most repeated values in all columns and show how many times has been repeated.

Thanks,

Answer 1

Try a query like this:

select top(10) with ties 
   Column1, Column2, Column3, Column4, Column5, Column6, 
   count(*) as [how many times]
from reader..dailyreader
group by Column1, Column2, Column3, Column4, Column5, Column6
order by [how many times] desc

If you mean something different, then show details or examples.

Answer 2

Hi @M Simo

If I understand correctly, you can try this query.

;with CTE as(
  select column1,column2,column3,column4,column5,column6,count(*) as nums,
         dense_rank()over(order by count(*) desc) as ord
  from reader.dailyreader group by column1,column2,column3,column4,column5,column6)
select * from CTE where ord = 1;

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I have a database called a “reader”, and a table called "dailyreader"

And now we have to guess the table design (DDL) and the existing values? No.

select the most repeated values in all columns

Repeating in which way? What for columns? What for data?