Sql select most repeated value
HTML-код
- Опубликовано: 4 окт 2024
- Sql query to select most frequent value | sql select most repeated column value | sql select most repeated table value | sql select most repeated value in a table column | sql query to select most frequently occurring value
Notes and Slides
www.pragimtech...
Group By in SQL Server
• Group by in sql server...
SQL Server Tutorial for beginners course page.
www.pragimtech...
SQL Server Tutorial for beginners playlist
• SQL Server tutorial fo...
SQL Server Interview Questions and Answers
• SQL Server Interview Q...
SQL Server Performance Tuning and Query Optimization
• SQL Server Performance...
Sir please make complete series on SQL server performance tuning.
Waiting for your 5th video in sql performance tuning series
please continue the sql series of performance tuning.
Yes
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
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.
Waiting for SQL server performance tuning videos...Very much helpful for us.
Your voice of teaching is awesome 😌
The best explanation i never ever heard.. superb and Thank you so much
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.
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
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;
U r great and true master for sql
Best online trainer 💪 👌
Thank you so much video was helpfull😊
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.
ruclips.net/p/PL6n9fhu94yhXg5A0Fl3CQAo1PbOcRPjd0
@@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
Nice sir
Please sir
make videos on SQL performance & tuning
you are awesom sir, just what I was looking for ..haha
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.
👌
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!
Hi sir ,please do video on performance tunning and query optimization
This is so useful. Thank you
.
Nice explained all the videos sir👍Great work
When can we expect new videos on azure? Eagerly waiting 😊 for that
Please continue with this series
Very clear explanation...tnx
Productive 📚
Tx u so much ❤️🙏 please keep doing more
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 ✌️
Thank you. keep posting new videos.
with cte as (select *, count(name) over(partition by name) rnk from nam)
select distinct(name) from cte where rnk=n;
Why don't you make a playlist for Azure Data Factory (the new SSIS) Venkat?
Thank you soo much sir for uploading this video on my request
Thanks a lot, really your videos are learnable ... please do more SQL PLSQL Interview questions
Please make more video likw this...
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);
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.
thanks Venkat. 👍
Thank you so much
Please make video on ....counting no. Of letters getting repeated in any word.....like crocodile have c letter 2 times
Thank you venkat sir🙂
Great tutorial.
Nice explanation 👌
Your voice make me feel cool
Sir txs.your video is helpful. Please make videos on how to change text to editable onclick in the text
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
thank you sir
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
)
Please try to give the real time scenarios also. Real world scenarios along with examples. It's request
Nice, thanks 👊
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.
please start for SQL JSON and SQL query optimization
thanks bro
How to get bottom N records from a table and don't want apply sorting. Pls guide me how to do
Thank you
Not able to execute the sql query..Getting below error Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@FirstNamesList".
ty
can we use it for int/float data type?
or
date datatype
Please make a series about Apache Cassandra
Plz continue sql performance series
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
make video on Data Structure sir
Sir are you going to cover MongoDB anytime soon?
Could you please help with finding records with null values
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/
Can you pls add a tutorial for WPF
hello,how can you place a unique value between 2 high values
just a heads up this wont work in mysql
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.
ruclips.net/p/PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR
SQL Server Tutorial for Beginners (All the basic and advanced concepts)
ruclips.net/p/PL08903FB7ACA1C2FB
SQL Server Performance Tuning and Query Optimization
ruclips.net/p/PL6n9fhu94yhXg5A0Fl3CQAo1PbOcRPjd0
@@Csharp-video-tutorialsBlogspot THANK YOU SO MUCH! 🙏🏻
Sir start big query please
if TOP 1 doesnt work , use LIMIT 1 at the end of query
Select name from (Select name, count (name) as value from tablename
Group by name)
Where value = 2
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.