Hey guys, I am Venkat and in this video, we'll discuss one of the common sql interview questions,
write a sql query to select the most repeated column value. In this "Students" table,
we have only one column, the "Name" column and the most repeated value in this column is "Tom",
because this name is repeated twice. So, we want to write a sql query that selects this value.
If you understand "Group By" in SQL, this query is very easy to write. After you
answer this question, the interviewer may ask the following related question as well
to check your understanding of "Group By" and "Having". Write a sql query
to find a value that is repeated n number of times, that is, maybe five times, ten times,
one million times who knows, again the key to answer these questions is understanding "group by"
and "having" in SQL. If you're new to these two concepts, please check out this video. I'll have
the link available in the description box below this video. Here is the SQL script to create and
insert test data into "Students" table, again I'll have the script available in the description box
below this video. Now, we want to write a sql query that selects the most repeated student name.
At the moment in our "Students" table as you can see, we've got four rows and "Tom"
is the most repeated column value, so we want to write a sql query that selects "Name" column
from "Students" table. Now, in the select list along with the "Name" of the student,
I also want the number of times each student name is repeated. For that,
in the select list along with the "Name" column, let's also use sql server aggregate function
"Count()" and then we can either specify star here or the name of the column by which we are
counting. In our case we are counting by the "Name" column, so let's use the column name to
keep it more meaningful, but keep in mind you can either use star or the name of the column,
it doesn't really matter, and let's give this column an alias "TotalRepetitions"
At this point, let's execute this query and see what happens.
We have an error, why? Well, that's because by using this "Count()" function we're asking
sql server to count the number of times each student name is repeated and for sql server to
be able to do that, it has to first group the rows by "Name" column. Let me actually explain
what I mean. First, let's select all the rows that we have in the "Students" table.
Now, for sql server to be able to count how many times each student name is repeated,
it has to group these rows that we have in the "Students" table by the "Name" column.
So, in the group "Sara" we only have one repetition, and in a group "Tom" we have two
repetitions because this name is repeated twice and in the group "Mike" we have one repetition,
so to be able to tell sql server to group these rows by the "Name" column, we use "Group By"
by which column we want to group? by by the "Name" column. Let's execute this query now. There we go,
by looking at this output we can say "Tom" is the most repeated name, but then in the output
we don't want all these groups, we just want this one group. So, for that let's sort the result set
by the "TotalRepetitions" column. So, let's use "Order By" and we want to order by
"TotalRepetitions" column in descending order. Let's execute this query again.
Notice, now in the result set we have the group with the highest repetitions at the top
and in the result we want just this one row so for that, in the select list include "Top 1"
There we go, in the output we have the "Name" that is repeated most number of times,
but then wait a minute, in the output we don't want this "TotalRepetitions" column,
we just want the "Name" value that is repeated most number of times. Well,
let's see what's going to happen if we drop this "TotalRepetitions" column from the select list
Let's execute the query. An error, invalid column name "TotalRepetitions", why? Well,
that's because sql server doesn't know what this "TotalRepetitions" column is,
why? Well, that's because we just deleted that column. To fix this, instead of using
the column alias "TotalRepetitions", let's use this expression directly, so order by Count(Name)
descending and we don't want this column in our select list, delete that and execute the query.
There we go, just the name of the student that's repeated most number of times. Now, let me insert
this name "Sara" three more times into the "Students" table. Select the data again,
now "Sara" is the most repeated name. So, if we execute our query, we should get "Sara",
there we go. Now, what if the interviewer asks you another follow-up question, write a sql query
to find the student name that is repeated exactly two times? Well, again as I said before,
if we understand "Group By" and "Having", this query is very easy to write. Now,
one important point to keep in mind, in SQL to filter groups we use "having" and
not the "where" clause. So, in place of this "Order By" let's use "Having"
Count(Name) = 2, why 2? Well, because we want the "Name" that is repeated
exactly two times. There we go, the name "Tom" is repeated two times and we see it in the output.
Now, what if you want all the names that are repeated two times? Well, for that
simply remove this "Top 1" keyword from the select list. If you want a student
name that is repeated exactly four times, then instead of "2" include "4" here. There we go,
we have Sara''s name which is repeated four times. That's it in this video, thank you for listening.