over() with rank() using MySQL table

over() & partition window

over() with rank() Using non-aggregate function RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(n) with OVER we can display row details along with these values.
RANK() OVER ([ partition_clause ] order_by)
Using student sample table.
SELECT id, name,class,mark,gender,rank() over(order by mark)  as 'r1'
FROM student WHERE class='Four' ;
id name class mark gender r1
6Alex JohnFour55male1
10Big JohnFour55female1
4Krish StarFour60female3
5John MikeFour60female3
21Babby JohnFour69female5
1John DeoFour75female6
15Tade RowFour88male7
16GimmyFour88male7
31 Marry ToeeyFour88male7
Note that there is no rank value 2 and 4 as there are same ranks for 1 and 3 positions.

DENSE_RANK()

There are some students with same marks. The function RANK() keeps gaps ( between ranks ) but DENSE_RANK() returns without gaps (between ranks ).
We will use DENSE_RANK with RANK and ROW_NUMBER for comparison.
SELECT id, name,class,mark,gender,
ROW_NUMBER() OVER(ORDER BY mark) as 'rn',
RANK() OVER(ORDER BY mark)  as 'r1',
DENSE_RANK() OVER(ORDER BY mark) as DR
FROM student WHERE class='Four';
id name class markgenderrn r1 DR
6Alex JohnFour55male111
10Big JohnFour55female211
4Krish StarFour60female332
5John MikeFour60female432
21Babby JohnFour69female553
1John DeoFour75female664
15Tade RowFour88male775
16GimmyFour88male875
31Marry ToeeyFour88male975
In case of DENSE_RANK no rank is skipped in case of tie over rank. If there is no duplicate value ( mark ) then there is no difference between RANK() and DENSE_RANK()

ROW_NUMBER()

Incremental numbers starting from 1 over the partition window. In case of tie same incremental ordering is maintained.
SELECT id, name,class,mark,gender,
ROW_NUMBER() OVER(ORDER BY mark) as 'rn'
FROM student WHERE class='Four';
Output
idnameclassmarkgenderrn
6 Alex JohnFour55male1
10Big JohnFour55female2
4Krish StarFour60female3
5John MikeFour60female4
21Babby JohnFour69female5
1John DeoFour75female6
15Tade RowFour88male7
16GimmyFour88male8
31Marry ToeeyFour88male9
Using Partition
SELECT id, name,class,mark,gender,
ROW_NUMBER() OVER(PARTITION BY gender ORDER BY mark) as 'rn'
FROM student WHERE class='Four';
Output
idnameclassmarkgenderrn
10 Big JohnFour55female1
4Krish StarFour60female2
5John MikeFour60female3
21Babby JohnFour69female4
1John DeoFour75female5
6Alex JohnFour55male1
15Tade RowFour88male2
16GimmyFour88male3
31Marry ToeeyFour88male4

NTITLE(n)

We can group the rows (buckets) by using NTILE(n). Here n is a positive integer.
SELECT id, name,class,mark,gender,
NTILE(3) OVER( ORDER BY mark) as my_NTILE
FROM student WHERE class='Four';
idnameclassmarkgendermy_NTILE
6Alex JohnFour55male1
10Big JohnFour55female1
4Krish StarFour60female1
5John MikeFour60female2
21Babby JohnFour69female2
1John DeoFour75female2
15Tade RowFour88male3
16GimmyFour88male3
31Marry ToeeyFour88male3

Over & partition window LEAD() LAG() Sum Multiple column


plus2net.com

Post your comments , suggestion , error , requirements etc here





SQL Video Tutorials










We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer