HomeОбразованиеRelated VideosMore From: kudvenkat

NTILE function in SQL Server

163 ratings | 36510 views
In this video we will discuss NTILE function in SQL Server NTILE function 1. Introduced in SQL Server 2005 2. ORDER BY Clause is required 3. PARTITION BY clause is optional 4. Distributes the rows into a specified number of groups 5. If the number of rows is not divisible by number of groups, you may have groups of two different sizes. 6. Larger groups come before smaller groups For example NTILE(2) of 10 rows divides the rows in 2 Groups (5 in each group) NTILE(3) of 10 rows divides the rows in 3 Groups (4 in first group, 3 in 2nd & 3rd group) Syntax : NTILE (Number_of_Groups) OVER (ORDER BY Col1, Col2, ...) SQL Script to create Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Gender nvarchar(10), Salary int ) Go Insert Into Employees Values (1, 'Mark', 'Male', 5000) Insert Into Employees Values (2, 'John', 'Male', 4500) Insert Into Employees Values (3, 'Pam', 'Female', 5500) Insert Into Employees Values (4, 'Sara', 'Female', 4000) Insert Into Employees Values (5, 'Todd', 'Male', 3500) Insert Into Employees Values (6, 'Mary', 'Female', 5000) Insert Into Employees Values (7, 'Ben', 'Male', 6500) Insert Into Employees Values (8, 'Jodi', 'Female', 7000) Insert Into Employees Values (9, 'Tom', 'Male', 5500) Insert Into Employees Values (10, 'Ron', 'Male', 5000) Go NTILE function without PARTITION BY clause : Divides the 10 rows into 3 groups. 4 rows in first group, 3 rows in the 2nd & 3rd group. SELECT Name, Gender, Salary, NTILE(3) OVER (ORDER BY Salary) AS [Ntile] FROM Employees What if the specified number of groups is GREATER THAN the number of rows NTILE function will try to create as many groups as possible with one row in each group. With 10 rows in the table, NTILE(11) will create 10 groups with 1 row in each group. SELECT Name, Gender, Salary, NTILE(11) OVER (ORDER BY Salary) AS [Ntile] FROM Employees NTILE function with PARTITION BY clause : When the data is partitioned, NTILE function creates the specified number of groups with in each partition. The following query partitions the data into 2 partitions (Male & Female). NTILE(3) creates 3 groups in each of the partitions. SELECT Name, Gender, Salary, NTILE(3) OVER (PARTITION BY GENDER ORDER BY Salary) AS [Ntile] FROM Employees Link for all dot net and sql server video tutorial playlists https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1 Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/ntile-function-in-sql-server.html
Html code for embedding videos on your blog
Text Comments (19)
Courage Osaro (2 months ago)
Awesome. Thank you Sir!
Jimmy T (6 months ago)
Of all of the SQL tutorials I have watched on youtube, your channel is the best. Great work mate!
Aziz Bassim (1 year ago)
nice videos. what is the business needs for ntile function?
Tom Martin (1 year ago)
I'm impressed with both the amount and quality of instructional videos you have created. I've found these videos HUGELY valuable in improving my understanding of a number of SQL functions. Thank you very much for sharing your knowledge.
prasanna g (1 year ago)
Best teacher I ever had in my life....awesome explanation
Krzysztof S (1 year ago)
Thank U for educating community. You are the best teacher on the Earth. God bless U and keep goin'. Best regards from beautiful Poland.
archrodney (2 years ago)
What is the real world use case of such grouping?
siddhartha rao (2 years ago)
Nice Explanation..
Pulin Jhaveri (2 years ago)
Thank you very much...
Roger Santos (3 years ago)
Thanks, nice explaning !!
Papa Collabo (3 years ago)
Thank you for this great tutorial, you are the best. thanks man
kudvenkat (3 years ago)
+Papa Collabo Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. Dot Net & SQL Server training videos for web developers https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd You can order DVDs for offline viewing using the link below http://www.pragimtech.com/Order.aspx Code Samples & Slides are on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use our channel https://www.youtube.com/watch?v=y780MwhY70s To receive email alerts, when new videos are uploaded, please subscribe to our channel http://www.youtube.com/subscription_center?add_user=kudvenkat Please click that THUMBS UP button below the video, if you like the videos Thanks a million for sharing these resources with your friends Best Venkat
Nayanajith Pilapitiya (3 years ago)
Thank you very much and it was really interesting. :)
Girijesh Kumar (3 years ago)
Thank you so much sir your all videos is awesome !!! and these are very helpful to clear my logic.
Hrushikesh Vartak (3 years ago)
Nice video! Thanks!
govindraj kannan (3 years ago)
Thank u very much sir
Vikas salve (3 years ago)
Thank You sir for uploading this video it's clear my concept
raqibul1000000 Alam (3 years ago)
Nice Tutorial.Go Ahead.
krutomjer (3 years ago)
Thanks man !

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.