AWS DATA ENGINEERING - 25. RANK, DENSE_RANK, ROW_NUMBER IN SQL |SQL Analytical Functions

RANK, DENSE_RANK, ROW_NUMBER IN SQL In SQL, ROW_NUMBER(), RANK(), and DENSE_RANK() are window functions that assign ranks or numbers to rows based on the order defined in the ORDER BY clause of the OVER() window function. They differ in how they handle ties (rows with the same values in the ordered column). 1. ROW_NUMBER() Purpose: Assigns a unique, sequential number to each row within a partition, based on the order specified. Behavior with Ties: Each row gets a unique number, so even rows with the same values in the order criteria will receive different row numbers. RANK() Purpose: Assigns a rank to each row within a partition, with the same rank given to rows with identical values in the ordered column. Behavior with Ties: Rows with the same values get the same rank. The next rank is incremented by the number of tied rows, which can cause gaps in the ranking. DENSE_RANK() Purpose: Similar to RANK(), it assigns ranks to rows within a partition, with the same rank given to tied rows. Behavior with Ties: Rows with identical values receive the same rank. However, DENSE_RANK() does not create gaps in the ranking sequence. Want more similar videos- hit like, comment, share and subscribe ❤️Do Like, Share and Comment ❤️ ❤️ Like Aim 5000 likes! ❤️ ➖➖➖➖➖➖➖➖➖➖➖➖➖ Please like & share the video. ➖➖➖➖➖➖➖➖➖➖➖➖➖ script CREATE TABLE employees1 ( employee_id INT, name VARCHAR(50), department VARCHAR(50), salary INT ); INSERT INTO employees1 (employee_id, name, department, salary) VALUES (1, 'Alice', 'Sales', 5000), (2, 'Bob', 'Sales', 3000), (3, 'Charlie', 'Sales', 3000), (4, 'David', 'Sales', 2000), (5, 'Eve', 'Sales', 2000); ➖➖➖➖➖➖➖➖➖➖➖➖➖ AWS DATA ENGINEER : https://www.youtube.com/playlist?list=PLOlK8ytA0MghpdMjb0m9zu1v9s_qbRP0q Azure data factory : https://youtube.com/playlist?list=PLOlK8ytA0MgguN5XidtQXbILxwCdJCUJE&si=iEICXHP80zGchUYh Azure data engineer playlist : https://youtube.com/playlist?list=PLOlK8ytA0MghBrzu0i6WlTBdoO1WdwV_e SQL PLAYLIST : https://www.youtube.com/playlist?list=PLOlK8ytA0MggGXIKmbfZ-_Xqcos3JKdV- PYSPARK PLAYLIST - https://www.youtube.com/playlist?list=PLOlK8ytA0MgjvOpd-088NRU_vTYw_aryH ➖➖➖➖➖➖➖➖➖➖➖➖➖ 📣Want to connect with me? Check out these links:📣 Join telegram to discuss https://t.me/+Cb98j1_fnZs3OTA1 ➖➖➖➖➖➖➖➖➖➖➖➖➖ what we have covered in this video: ➖➖➖➖➖➖➖➖➖➖➖➖➖ Hope you liked this video and learned something new :) See you in next video, until then Bye-Bye! ➖➖➖➖➖➖➖➖➖➖➖➖➖