very straight forward and to the point .. one issue, not major but should be accounted for, is if two names appear as the, say Tom appears 2 times and Sarah appears 2 times you would want both returned since they are both the most. The Top 1 clause won't work for you.
Good explanation!. Just want to add my understanding if anyone is new to learning SQL.. At 05:40 timestamp.... Syntax of GROUP BY clause as follows: SELECT Column name FROM Table name [WHERE condition] GROUP Column name [HAVING Condition] [ORDER BY column name ASC/DESC] 1. We will use HAVING if it contains aggregate functions: Per syntax (above), HAVING condition is used only after GROUP BY. which means aggregate functions (Count, Avg, Min, Max, Sum) works post grouping of the column. 2. We will use WHERE if it DOES NOT contain aggregate functions: Per Syntax, WHERE is used only before GROUP BY, which means we are not using any calculation part here. 3. Grouping is not mandatory for WHERE condition where as it is mandatory for HAVING. 4. In SQL [..] brackets are used if a condition is optional*. Open for suggestions/comments/Dislikes/Likes :). Thank you for your time and have a good day, Happy Learning!
There's a fallicy in your query. You need to actually do a RANK() OVER PARTITION BY NAME ORDER BY COUNT DESCENDING in a sub query and join that back to your parent table where the rank = 1. This will give you the first place name, and also account for when there might be >1 name repeated the same amount of times.
Venkat sir you are my all time favourite. Requesting you to please please create a series for query optimisation and performance tuining We would be thankful to you.
We already have a series on SQL Server Performance Tuning and Query Optimization. Hope you will find it useful. th-cam.com/play/PL6n9fhu94yhXg5A0Fl3CQAo1PbOcRPjd0.html
@@Csharp-video-tutorialsBlogspot thank you for your response.you are my guru and I am Highly obliged to see your response. Sir requesting you to please complete this with your ocean of knowledge like you did for General sql or if you already teach optimisation & performance tuining thing in paid form in online platform under some course I am ready to buy that
@@salarkazazi7584 when we dont use TOP keyword we get all COUNT's then only we would know like what we could do with it. As shown in this video after using TOP 1 we get a single value. don't forget order by though.
Try this: SELECT Name FROM Students GROUP BY Name HAVING COUNT(Name)=(SELECT TOP 1 COUNT(Name) FROM Students GROUP BY Name ORDER BY COUNT(Name) DESC) ORDER BY Name
Hi Sir, At video running time 3:55 if Mike/Sara has the count 2, then the query won't help to find the most repeated value. I mean if there is a tie between the most repeated value I found the following query helpful SELECT Name FROM Students GROUP BY Name HAVING COUNT(Name) = ( SELECT MAX(TotalRepetitions) FROM ( SELECT COUNT(NAME) AS TotalRepetitions,Name FROM Students GROUP BY Name ) AS result ) Please let us know if there is an optimized way to achieve it.
thank you.. alternative answer for n times with result as ( select name , count(*) as newtable from students group by name order by count(*) ) select * from result where result.newtable = 1;
Very helpful..kindly share same kind of videos. More difficult queries to solve which is helpful in Interviews. You can find questions on coding but you can't find difficult queries on the internet. So it will be helpful ✌️
Hello sir, if the most repeated values for more than one name then this top 1 will return the very first row. But we have to return all name for most repeated values.
Hi Tanuj - It's part of SQL Server Interview Questions and Answers playlist at the following link. th-cam.com/play/PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR.html
SQL Server Tutorial for Beginners (All the basic and advanced concepts) th-cam.com/play/PL08903FB7ACA1C2FB.html SQL Server Performance Tuning and Query Optimization th-cam.com/play/PL6n9fhu94yhXg5A0Fl3CQAo1PbOcRPjd0.html
This is a good question, and I'd generally echo Jim's response - you learn best when you try for yourself, especially if something unexpected happens. However, if you try it, the answer alone would give you an incomplete picture, so I'd recommend also investigating the 'WITH TIES' clause.
It will be a random result with the top count unless you have specified additional order by values. If you want to receive all of the results with the top count you can run something like this: select Name from Students group by Name having count(Name) = ( select top 1 count(Name) from Students group by Name order by count(Name) desc )
@@cyb3r1 This was the reason for my hint. If you want all names with the top count, you can achieve the same result but simpler like this: SELECT TOP 1 WITH TIES Name FROM Students GROUP BY Name ORDER BY COUNT(Name) DESC "WITH TIES" allows any matching results at the end of the result set to overflow the usual TOP number limit.
Hello Goutham - Locks are discussed in SQL Server Tutorial for beginners course. Please check out videos from Part 78 to 86. Hope you will find them useful. www.pragimtech.com/courses/sql-server-tutorial-for-beginners/
I haven't used SQL in a while as my current job didn't require it, your videos keeps me in touch with SQL and stops me from forgetting it. Thanks
please continue the sql series of performance tuning.
Yes
Sir please make complete series on SQL server performance tuning.
very straight forward and to the point .. one issue, not major but should be accounted for, is if two names appear as the, say Tom appears 2 times and Sarah appears 2 times you would want both returned since they are both the most. The Top 1 clause won't work for you.
God bless you sir. I am your very old student. I have learned .net by watching your lectures after I did my MCA.
Thanks for your teachings.
Waiting for your 5th video in sql performance tuning series
Good explanation!. Just want to add my understanding if anyone is new to learning SQL..
At 05:40 timestamp....
Syntax of GROUP BY clause as follows:
SELECT Column name
FROM Table name
[WHERE condition]
GROUP Column name
[HAVING Condition]
[ORDER BY column name ASC/DESC]
1. We will use HAVING if it contains aggregate functions: Per syntax (above), HAVING condition is used only after GROUP BY. which means aggregate functions (Count, Avg, Min, Max, Sum) works post grouping of the column.
2. We will use WHERE if it DOES NOT contain aggregate functions: Per Syntax, WHERE is used only before GROUP BY, which means we are not using any calculation part here.
3. Grouping is not mandatory for WHERE condition where as it is mandatory for HAVING.
4. In SQL [..] brackets are used if a condition is optional*.
Open for suggestions/comments/Dislikes/Likes :). Thank you for your time and have a good day, Happy Learning!
The best explanation i never ever heard.. superb and Thank you so much
Waiting for SQL server performance tuning videos...Very much helpful for us.
There's a fallicy in your query. You need to actually do a RANK() OVER PARTITION BY NAME ORDER BY COUNT DESCENDING in a sub query and join that back to your parent table where the rank = 1. This will give you the first place name, and also account for when there might be >1 name repeated the same amount of times.
good job pro , but i thinks it will be very difficult to catch it think this way it easy
this was the exact video which I want , thanks a lot for your wonderful explanation sir
Venkat sir you are my all time favourite.
Requesting you to please please create a series for query optimisation and performance tuining
We would be thankful to you.
We already have a series on SQL Server Performance Tuning and Query Optimization. Hope you will find it useful.
th-cam.com/play/PL6n9fhu94yhXg5A0Fl3CQAo1PbOcRPjd0.html
@@Csharp-video-tutorialsBlogspot thank you for your response.you are my guru and I am
Highly obliged to see your response.
Sir requesting you to please complete this with your ocean of knowledge like you did for General sql or if you already teach optimisation & performance tuining thing in paid form in online platform under some course I am ready to buy that
Thank you so much, what if there were equal repeated values? like 2 Toms and 2 Sarahs and we wanna show them both?
Instead of 'TOP 1' use 'TOP 2'
@@programit7155 What if we dont know how many there are?Dynamic way of responding i meant
@@salarkazazi7584 when we dont use TOP keyword we get all COUNT's then only we would know like what we could do with it. As shown in this video after using TOP 1 we get a single value. don't forget order by though.
@@programit7155 Thank you
Try this:
SELECT Name
FROM Students
GROUP BY Name
HAVING COUNT(Name)=(SELECT TOP 1 COUNT(Name) FROM Students GROUP BY Name ORDER BY COUNT(Name) DESC)
ORDER BY Name
Hi Sir,
At video running time 3:55 if Mike/Sara has the count 2, then the query won't help to find the most repeated value.
I mean if there is a tie between the most repeated value
I found the following query helpful
SELECT Name
FROM Students
GROUP BY Name
HAVING COUNT(Name) = (
SELECT MAX(TotalRepetitions)
FROM (
SELECT COUNT(NAME) AS TotalRepetitions,Name
FROM Students
GROUP BY Name
) AS result
)
Please let us know if there is an optimized way to achieve it.
Select Name
from Students
group by Name
having count(*) =(
Select top 1 count(name) from Students group by name order by count(name) desc
)
thank you.. alternative answer for n times
with result as (
select name , count(*) as newtable from students group by name order by count(*)
)
select * from result where result.newtable = 1;
Why don't you make a playlist for Azure Data Factory (the new SSIS) Venkat?
U r great and true master for sql
Can’t we use “where count(name) = 2” as well?
Aggregate functions such as count, min, max are handled by HAVING Clause and not by WHERE Clause.
Your voice of teaching is awesome 😌
Very helpful..kindly share same kind of videos. More difficult queries to solve which is helpful in Interviews.
You can find questions on coding but you can't find difficult queries on the internet. So it will be helpful ✌️
Nice video but being your old subscriber I am expecting some more advance topics like you did before e.g. how to sql index work
Keep it bro good work.
Hello sir, if the most repeated values for more than one name then this top 1 will return the very first row. But we have to return all name for most repeated values.
can we use it for int/float data type?
or
date datatype
Best online trainer 💪 👌
Not able to execute the sql query..Getting below error Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@FirstNamesList".
Hi sir ,please do video on performance tunning and query optimization
Nice explained all the videos sir👍Great work
If I have a data of day 1 to 100 and I want to find which number is missing in between which query to fire pls help
How to get bottom N records from a table and don't want apply sorting. Pls guide me how to do
Thanks a lot, really your videos are learnable ... please do more SQL PLSQL Interview questions
When can we expect new videos on azure? Eagerly waiting 😊 for that
Could you please help with finding records with null values
This is so useful. Thank you
.
hello,how can you place a unique value between 2 high values
Thank you. keep posting new videos.
you are awesom sir, just what I was looking for ..haha
Thank you soo much sir for uploading this video on my request
Are these not being put in a playlist? There is one SQL playlist with 150 videos, is this video part of that series?
Hi Tanuj - It's part of SQL Server Interview Questions and Answers playlist at the following link.
th-cam.com/play/PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR.html
SQL Server Tutorial for Beginners (All the basic and advanced concepts)
th-cam.com/play/PL08903FB7ACA1C2FB.html
SQL Server Performance Tuning and Query Optimization
th-cam.com/play/PL6n9fhu94yhXg5A0Fl3CQAo1PbOcRPjd0.html
@@Csharp-video-tutorialsBlogspot THANK YOU SO MUCH! 🙏🏻
Very clear explanation...tnx
Hi. If two different records have same count, what will be result ?
Why don’t you run it and find out
This is a good question, and I'd generally echo Jim's response - you learn best when you try for yourself, especially if something unexpected happens. However, if you try it, the answer alone would give you an incomplete picture, so I'd recommend also investigating the 'WITH TIES' clause.
It will be a random result with the top count unless you have specified additional order by values. If you want to receive all of the results with the top count you can run something like this:
select Name
from Students
group by Name
having count(Name) =
(
select top 1 count(Name)
from Students
group by Name
order by count(Name) desc
)
@@chezchezchezchez I did it, before)
@@cyb3r1 This was the reason for my hint. If you want all names with the top count, you can achieve the same result but simpler like this:
SELECT TOP 1 WITH TIES Name
FROM Students
GROUP BY Name
ORDER BY COUNT(Name) DESC
"WITH TIES" allows any matching results at the end of the result set to overflow the usual TOP number limit.
Hello venkat if possible could u please make a tutorial video about sql locks, it would be much appreciated
Hello Goutham - Locks are discussed in SQL Server Tutorial for beginners course. Please check out videos from Part 78 to 86. Hope you will find them useful.
www.pragimtech.com/courses/sql-server-tutorial-for-beginners/
Sir txs.your video is helpful. Please make videos on how to change text to editable onclick in the text
thanks Venkat. 👍
Tx u so much ❤️🙏 please keep doing more
Nice sir
Please sir
make videos on SQL performance & tuning
Great tutorial.
with cte as (select *, count(name) over(partition by name) rnk from nam)
select distinct(name) from cte where rnk=n;
Thank you venkat sir🙂
Thank you so much video was helpfull😊
Nice, thanks 👊
Sir are you going to cover MongoDB anytime soon?
👌
with cte as(select *,row_number() over(partition by name) as rnk from nam)
select name from cte where rnk = (select max(rnk) from cte);
Please make video on ....counting no. Of letters getting repeated in any word.....like crocodile have c letter 2 times
Please continue with this series
please start for SQL JSON and SQL query optimization
Productive 📚
Please try to give the real time scenarios also. Real world scenarios along with examples. It's request
Nice explanation 👌
Your voice make me feel cool
Can you pls add a tutorial for WPF
Thank you so much
Please make a series about Apache Cassandra
Thank you
just a heads up this wont work in mysql
thank you sir
Please make more video likw this...
thanks bro
Plz continue sql performance series
ty
make video on Data Structure sir
Select name from (Select name, count (name) as value from tablename
Group by name)
Where value = 2
Sir start big query please
if TOP 1 doesnt work , use LIMIT 1 at the end of query