How to Calculate Mode in SQL | How to Find Most Frequent Value in a Column

In this video we will learn 2 methods of how to calculate mode in SQL.
First method will be using pure CTE. Second method will be using Rank function.
Mode is nothing but most frequent value in an Array.

create table mode
(
id int
);

insert into mode values (1),(2),(2),(3),(3),(3),(3),(4),(5);

SQL basics | how to find mode | sql interview questions

[divyaaggarwal]

I am preparing for interviews and I just came across your channel. The videos are really informative. Thankyou so much !! 👍🏻
Keep up the good work, buddy.

[sanjeetsingh]

Great solutions in simple way. I would like to share my interview experience.. This was the 1st questions asked in OPTUM(UHG) interview.. both using SQL & Tableau ..
@Ankit your videos are really very helpfull in clearing concepts & basics .. keep up the good work :)

[RoshniNV-vd]

This was asked in my interview yesterday, I wish I had watched this earlier, but now I’m damn sure I won’t forget how to tackle these kind of problems, thank you so much Ankit bhaiya 💯

[anujgupta]

with cte as(
select id, row_number() over (partition by id order by id) most_time from mode)
select * from cte where most_time =
(select max(most_time) from cte)

[shaktijyoti]

Thanks Brother for your precise explanation, though I had the knowledge about joins still I got some useful insights.

[My_Soul_melodies]

Great explanation sir, I have also solved it but using subquery and rank function without CTE

[hairavyadav]

Hello sir please let me know this will work or not
with cte as(select *, row_number() over(partition by id) rnk from mode)
select id from cte where rnk in (select max(rnk) from cte);

[soumyagobbani]

cte as (select id, rank() over (order by count(*) desc) rwn from mode group by id)
select * from cte where rwn =1

[neelshah]

Can you please make videos on python and ML

[CloudDataEngineer]

;WITH cte
AS (SELECT id,
RANK() OVER (ORDER BY COUNT(id) DESC) AS RNK
FROM dbo.mode
GROUP BY id)
SELECT id
FROM cte
WHERE RNK = 1;

[grim_rreaperr]

SELECT TOP 1 WITH TIES *, COUNT(1) AS frequency FROM mode
GROUP BY id
ORDER BY COUNT(1) DESC;

[rishikeshdwivedi-xr]

Here is my Code:
select top 1 id, count(*) as Frequency from mode
group by id order by count(*) desc

[gauravpanchariya]

Your videos are really helpful in preparation of SQL. I subscribed your channel to learn more and in detail about SQL.

[kanchankumar]

Much helpful! Thanks for putting it up

[anishchhabra]

Excellent explanation bro! I think instead of 2 cte's we can use this code
with cte as
(
select *,
rank() over(order by count(id) desc) as rnk
from mode
group by id
)
select id
from cte
where rnk = 1;

[armanmardhani]

select id, count(id) as occurrences from modes group by id having count(id) = (select max(count(id)) from modes group by id)
How about this?