I have said this before about you, but it bears repeating. You are the foremost expert on grouping data. I appreciate your videos and they have helped me a lot.
Venkat rules! I'm the biggest fan of your tutorials. You are the best teacher in the world. God bless you for all your work and achievements. I learned a lot from your videos. Your explanations are fantastic and the way of your teaching is superb. Greatings from Poland !
Hi Venkat, I wanted to take a moment to express my heartfelt gratitude for your exceptional teaching of programming. Your dedication and expertise have had a tremendous impact on my learning journey, and I am incredibly grateful for your guidance. Your ability to break down complex programming concepts into understandable chunks truly impressed me. Through your clear explanations and engaging teaching style, you made learning programming enjoyable and accessible.
Hello Srujana - Thank you. Glad you are finding the videos useful. Means a lot. I have included all the SQL Course videos, slides and text articles in sequence on the following page. Hope you will find it handy. www.pragimtech.com/courses/sql-server-tutorial-for-beginners/ If you have time, can you please leave your rating and valuable feedback on the reviews tab. It really helps us. Thank you. Good luck and all the very best with everything you are doing.
+Dean Brandenburg Thanks a bunch for taking your valuable time to give feedback. Means a lot. Glad you found the videos useful. Dot Net & SQL Server videos for aspiring software developers ruclips.net/user/kudvenkatplaylists?view=1&sort=dd If you need videos for offline viewing, you can order them using the link below www.pragimtech.com/Order.aspx Code Samples, Text Version of the videos & PPTS on my blog csharp-video-tutorials.blogspot.com Tips to effectively use our channel ruclips.net/video/y780MwhY70s/видео.html Want to receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below ruclips.net/user/kudvenkat Please click the THUMBS UP button below the video, if you think you liked them Thank you for sharing these links with your friends Best Venkat
Thank you Venkat. I need some more information on this with join condition. Example i have employee table which has eno,ename,esal,edeptno columns, and another table (dept) which has dno,dname. Now i want to get the second max salary of "dname='Maths'". Please help me on this.
With EmployeeCTE as ( select emp.Name as [Employee Name], Gender, Salary, dept.Name as [Department Name], Dense_Rank() over (partition by dept.Name order by Salary desc) DenseRank from tblEmployees emp inner join tblDepartment dept on emp.DepartmentId = dept.DepartmentId ) Select Salary from EmployeeCTE where DenseRank = 2 and [Department Name]='Maths' With the above inner query in EmployeeCTE, we will get all the employee details like Employee Name, Gender, Salary, Department Name and Dense Rank partitioned by each department. Finally, we are getting second highest paid salary for the department "Maths" from the EmployeeCTE.
I have a problem, where i am required to determine a Rank on a table on the basis of a derived column. Say first column desired is Name of Port, 2nd column is the sum of profit made on that port. The third column should be the rank based on the highest profit the port has. How can i create an sql query of rank on a derived column like the sum of total profit for each port? Hopefully you can give a suitable example.
If we have 3 employees with second highest paid salary, we will get Salary three times in the result CTE. In order to avoid such kind of duplicate salaries, we can use either "top 1" or "distinct" keyword in the final select query.
declare @tbl table ( id int ) insert into @tbl values (1),(1),(3),(4),(5) select rank() over (order by id) as Rank_, DENSE_RANK()over (order by id) as Dense_rank_ from @tbl
want to find tob 5 highest product price in each category.In this case if there are one or more products with same price and if I use rank() then I get more than two record with rank 1 but I can't get rank with value 2 because there is tie.But in this case if I use dense_rank() then I get more than one records with dense_rank() of value 1 2 3 4 5.Please help
Hi Venkat, Am very happy to see your videos. i have a small doubt in this video with the below query. with Result as ( select salary, RANK() over (order by salary desc) as salaryrank from [dbo].[dense_employees] ) SELECT TOP 1 salary from Result where salaryrank=1 and my table structure is: CREATE TABLE [dbo].[dense_employees]( [id] [int] NULL, [name] [varchar](30) NULL, [gender] [varchar](10) NULL, [salary] [int] NULL ) ON [PRIMARY] when am executing the above query in 2012 sql server its not giving output.its showing that "incorrect syntax near result". Please guide me how to execute this in 2012 sqlserver. it will be great if can you can help on this. when am browsing the internet some sites showing "with result sets" other than "with result as" Please guide me on this . Thnaks, Krishna
What is with this fake accent man? It makes it so hard to focus on the lesson without getting irritated out of your mind. Who are you? Priyanka Chopra?
![I.N "HALLUCINATION" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/n5B5q1Hwt_U/mqdefault.jpg)
I have said this before about you, but it bears repeating. You are the foremost expert on grouping data. I appreciate your videos and they have helped me a lot.
Venkat rules! I'm the biggest fan of your tutorials. You are the best teacher in the world. God bless you for all your work and achievements. I learned a lot from your videos. Your explanations are fantastic and the way of your teaching is superb. Greatings from Poland !
Hi Venkat,
I wanted to take a moment to express my heartfelt gratitude for your exceptional teaching of programming. Your dedication and expertise have had a tremendous impact on my learning journey, and I am incredibly grateful for your guidance.
Your ability to break down complex programming concepts into understandable chunks truly impressed me. Through your clear explanations and engaging teaching style, you made learning programming enjoyable and accessible.
Omg...you are an amazing teacher. I feel too bad about not finding your tutorials until now. THANK YOU!
Hello Srujana - Thank you. Glad you are finding the videos useful. Means a lot. I have included all the SQL Course videos, slides and text articles in sequence on the following page. Hope you will find it handy.
www.pragimtech.com/courses/sql-server-tutorial-for-beginners/
If you have time, can you please leave your rating and valuable feedback on the reviews tab. It really helps us. Thank you. Good luck and all the very best with everything you are doing.
Great explanation. One of the very few tutorials I have seen which is very clear and still very precise. Super job! Thanks Venkat
That voice is ASMR to my ears.😌
Thank you for all your tutorials..I love your explanations and your soothing voice.I wish I had your brain,you make it look so easy,lol.
Everytime I watch venkat tutorials, I like the video first and then I start. 👍
thanks. this is the best explanation that I have seen for this.
+Dean Brandenburg Thanks a bunch for taking your valuable time to give feedback. Means a lot. Glad you found the videos useful.
Dot Net & SQL Server videos for aspiring software developers
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
If you need videos for offline viewing, you can order them using the link below
www.pragimtech.com/Order.aspx
Code Samples, Text Version of the videos & PPTS on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use our channel
ruclips.net/video/y780MwhY70s/видео.html
Want to receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below
ruclips.net/user/kudvenkat
Please click the THUMBS UP button below the video, if you think you liked them
Thank you for sharing these links with your friends
Best
Venkat
This was the best explanation ever. Thank you for that!
Although this is an old video the explanation is a real gem. Timeless knowledge
Thanks a lot for ur explanation.The highlight is the clarity and simplicity of explanation
Thanks very much for these videos.They are simple to understand and really helpful in learning query writing.
thanks for all your tutorials and clear explanations
Super awesome, your explanations are truly good. I really learnt a lot from it....Thank you so much!!
This helped me understand Rank better. Thank you.
you guys are great! I learned a lot from your videos
hats off to you.. you are a great teacher..
Great Videos and clear Explanation.Thanks a lot!!! it is very useful
Hi Venkat, you are the best!!
Thanks Professor..tutorial is quite helpful.
Nice Explanation, Thankyou so much.
Hi
Can we use rank, dense_rank or row_number functions over varchar columns?
Yes
Thanks venkat sir!!
Thanks a lot, sir.
Thank you.. Very nice tutorial
Very informative
Awesome teaching...👌👌👌
Thank You Professor.
Hello sir, pls make video on table partitioning and their concept...this is something important which is not made yet
Fantastic
Thank you so much
Nice explanation skills
Thank u bro that was helpful
you are the great sir
What are the advantages using of Rank() compared to GROUP BY or DISTINCT?
u cant group by for each record brother there will be only one record for each group. Rank with Over clause will give you each record separately.
Thanka a lot sir
EXCELLENT
Please post video on how to populate calender
When you use Top 1 then it should be returning 1st row then what's the use of where clause with it?
Thanks a lot
Thank you!
what is the use case of Rank function.Is there any scenario where we will use Rank?
Thank you Venkat. I need some more information on this with join condition. Example i have employee table which has eno,ename,esal,edeptno columns, and another table (dept) which has dno,dname. Now i want to get the second max salary of "dname='Maths'". Please help me on this.
With EmployeeCTE as
(
select emp.Name as [Employee Name], Gender, Salary, dept.Name as [Department Name],
Dense_Rank() over (partition by dept.Name order by Salary desc) DenseRank
from tblEmployees emp
inner join tblDepartment dept on emp.DepartmentId = dept.DepartmentId
)
Select Salary from EmployeeCTE where DenseRank = 2 and [Department Name]='Maths'
With the above inner query in EmployeeCTE, we will get all the employee details like Employee Name, Gender, Salary, Department Name and Dense Rank partitioned by each department. Finally, we are getting second highest paid salary for the department "Maths" from the EmployeeCTE.
I have a problem, where i am required to determine a Rank on a table on the basis of a derived column. Say first column desired is Name of Port, 2nd column is the sum of profit made on that port. The third column should be the rank based on the highest profit the port has. How can i create an sql query of rank on a derived column like the sum of total profit for each port? Hopefully you can give a suitable example.
Bro thank you!!!!
Thanks a trillion
Hello Venkat, you are my Dhronacharya,Thanks lot. Why we need to use top 1 in Result when we are filtering with Dense_Rank?
If we have 3 employees with second highest paid salary, we will get Salary three times in the result CTE. In order to avoid such kind of duplicate salaries, we can use either "top 1" or "distinct" keyword in the final select query.
coooooooool tutorial!!!
Why is it desc because I see the values in Ascending order so can someone give me why?
What if there are 100Million rows in this table? Won't the order by take a lot of time while ranking
declare @tbl table
(
id int
)
insert into @tbl values (1),(1),(3),(4),(5)
select
rank() over (order by id) as Rank_,
DENSE_RANK()over (order by id) as Dense_rank_
from @tbl
Hi, How can I select top 3 of DENSE_RANK() ?
thanks you
want to find tob 5 highest product price in each category.In this case if there are one or more products with same price and if I use rank() then I get more than two record with rank 1 but I can't get rank with value 2 because there is tie.But in this case if I use dense_rank() then I get more than one records with dense_rank() of value 1 2 3 4 5.Please help
Try select distinct rather than regular select with rank()
@@aaron2cool4u Thanks.. I had the same question.
clean
What’s tie? I can’t understand it.
notice in salary column there is two values 8000
this is called tie (when there is more one value is similar)
Super super super duper
how can I rank whole record only 1 through 10? not top n
use a CTE to rank them first and then select from the CTE where rank between to
Can we start rank from a number we need ? Like 1000 ?
Hi Venkat,
Am very happy to see your videos.
i have a small doubt in this video with the below query.
with Result as
(
select salary,
RANK() over (order by salary desc) as salaryrank
from [dbo].[dense_employees]
)
SELECT TOP 1 salary from Result where salaryrank=1
and my table structure is:
CREATE TABLE [dbo].[dense_employees](
[id] [int] NULL,
[name] [varchar](30) NULL,
[gender] [varchar](10) NULL,
[salary] [int] NULL
) ON [PRIMARY]
when am executing the above query in 2012 sql server its not giving output.its showing that "incorrect syntax near result".
Please guide me how to execute this in 2012 sqlserver.
it will be great if can you can help on this.
when am browsing the internet some sites showing "with result sets" other than "with result as"
Please guide me on this .
Thnaks,
Krishna
Rank will omit whereas dense rank will not omit
Ritesh Deshmukh
Mechanical
Madhuri Dixit
Anil Kapoor
Rank =1
What is with this fake accent man? It makes it so hard to focus on the lesson without getting irritated out of your mind. Who are you? Priyanka Chopra?
Thanks!