Rank and Dense Rank in SQL Server

[@krzysztofs8535]

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 !

[@88spaces]

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.

[@chetanchaudhari7538]

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.

[@dhamma1283]

Everytime I watch venkat tutorials, I like the video first and then I start. 👍

[@shradarai1068]

That voice is ASMR to my ears.😌

[@poorvi1036]

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.

[@srujanareddy5185]

Omg...you are an amazing teacher. I feel too bad about not finding your tutorials until now. THANK YOU!

[@Csharp-video-tutorialsBlogspot]

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.

[@rajreddy2561]

Great explanation. One of the very few tutorials I have seen which is very clear and still very precise. Super job! Thanks Venkat

[@sumaiyamohiyuddin8173]

Thanks a lot for ur explanation.The highlight is the clarity and simplicity of explanation

[@gurjeet333]

Although this is an old video the explanation is a real gem. Timeless knowledge

[@liverickoben9922]

thanks for all your tutorials and clear explanations

[@dbin805]

thanks. this is the best explanation that I have seen for this.

[@Csharp-video-tutorialsBlogspot]

+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 kzbin.infoplaylists?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 kzbin.info/www/bejne/r2ibYYCtnb5qZtU Want to receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below kzbin.info 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

[@awengirr]

This was the best explanation ever. Thank you for that!

[@deepakpangali1315]

Super awesome, your explanations are truly good. I really learnt a lot from it....Thank you so much!!

[@mariorivera6292]

This helped me understand Rank better. Thank you.

[@pavithravvenu5817]

Great Videos and clear Explanation.Thanks a lot!!! it is very useful

[@absneo247]

Thanks very much for these videos.They are simple to understand and really helpful in learning query writing.

[@soniasadeque4248]

hats off to you.. you are a great teacher..

[@vishnuvardhanreddy5986]

Nice Explanation, Thankyou so much.

[@fikirtezewdie5633]

Hi Venkat, you are the best!!

[@lilarasekh3818]

you guys are great! I learned a lot from your videos

[@mscreations183]

Awesome teaching...👌👌👌

[@TheHimaBindu]

Very informative

[@chandrakumarreddyvennapusa7806]

Fantastic

[@muhammadrehbarsheikh8498]

Thanks venkat sir!!

[@swetapanigrahi8001]

Thank you.. Very nice tutorial

[@shariqahmed9416]

Nice explanation skills

[@optimistic_guy313]

Thank you so much

[@nagatmohamed8246]

Thanks a lot, sir.

[@yogeshghule5260]

you are the great sir

[@keyvansadeghi5743]

Thank u bro that was helpful

[@102RC]

Thank You Professor.

[@anish_k]

Thanka a lot sir

[@TheSwatirock]

Thanks Professor..tutorial is quite helpful.

[@ting-kangpai1580]

Thank you!

[@korrapativinodkumar4760]

EXCELLENT

[@annwayde129]

Thanks a lot

[@samirkumar6180]

Hello sir, pls make video on table partitioning and their concept...this is something important which is not made yet

[@raqibul1000]

Thanks a trillion

[@jorgegonzaloalfaro5378]

Bro thank you!!!!

[@praburajiiv]

clean

[@bhavyashreesarvamangala6775]

Super super super duper

[@drallisimo34]

coooooooool tutorial!!!

[@prashantparmar1397]

Please post video on how to populate calender

[@quanhoang2139]

thanks you

[@wwewwf324]

what is the use case of Rank function.Is there any scenario where we will use Rank?

[@surabhichouhan3758]

Hi Can we use rank, dense_rank or row_number functions over varchar columns?

[@analytikmindz5390]

Yes

[@aadityasharma9024]

When you use Top 1 then it should be returning 1st row then what's the use of where clause with it?

[@anirbandas2062]

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.

[@JohnDoe-dy4kf]

What are the advantages using of Rank() compared to GROUP BY or DISTINCT?

[@ManishKumar-qx1kh]

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.

[@praveennagarajan1407]

Hi, How can I select top 3 of DENSE_RANK() ?

[@richardfrimpong3962]

Why is it desc because I see the values in Ascending order so can someone give me why?

[@jsj14]

What if there are 100Million rows in this table? Won't the order by take a lot of time while ranking

[@supercatsmari]

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.

[@MaheshBabu-vq7qk]

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.

[@adithyagarigipati4205]

Can we start rank from a number we need ? Like 1000 ?

[@rameshr5809]

Hello Venkat, you are my Dhronacharya,Thanks lot. Why we need to use top 1 in Result when we are filtering with Dense_Rank?

[@MaheshBabu-vq7qk]

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.

[@Gamezone-kq5sx]

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

[@aaron2cool4u]

Try select distinct rather than regular select with rank()

[@AmitKumar-fr7gb]

@@aaron2cool4u Thanks.. I had the same question.

[@shivarjunroy1394]

Rank will omit whereas dense rank will not omit

[@gurungoracle]

how can I rank whole record only 1 through 10? not top n

[@ManishKumar-qx1kh]

use a CTE to rank them first and then select from the CTE where rank between to

[@maryam4071]

What’s tie? I can’t understand it.

[@nys8260]

notice in salary column there is two values 8000 this is called tie (when there is more one value is similar)

[@krishnachaitanya9367]

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

[@shivarjunroy1394]

Ritesh Deshmukh

[@shivarjunroy1394]

Madhuri Dixit

[@shivarjunroy1394]

Mechanical

[@shivarjunroy1394]

Rank =1

[@shivarjunroy1394]

Anil Kapoor

[@saahilsud8933]

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?

[@shaikhzoyeb]

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

[@alinemath5030]

Thanks!