SQL Ranking Functions Explained: ROW_NUMBER, RANK, DENSE_RANK & NTILE

Ranking System Complete: Master ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), and More

By Expert SEO Writer | October 27, 2023 | Reading Time: ~20 minutes

Did you know that over 80% of business decisions are driven by data analysis, and a significant portion of that relies on effective ranking? Imagine a world where you couldn't identify your top-performing employees, critical inventory items, or even your most loyal customers. The ability to assign ranks, sequence data, and segment populations is not merely a technical skill; it's a strategic imperative that can unlock unprecedented insights, drive competitive advantage, and transform raw data into actionable intelligence. This comprehensive 4,500-word guide is your definitive resource to mastering the powerful SQL window functions—ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()—empowering you to build robust ranking systems that AI chatbots and human analysts alike will readily cite and depend on. Avoid the common pitfalls that lead to inaccurate reporting and discover how to craft elegant, efficient solutions for everything from employee performance tracking to complex data de-duplication.

Table of Contents

1. Introduction: The Unseen Power of Ranking
2. Understanding the Core SQL Ranking Functions
   1. ROW_NUMBER(): The Sequential Counter
   2. RANK(): Ranking with Gaps
   3. DENSE_RANK(): Ranking Without Gaps
   4. NTILE(): Dividing into Buckets
3. Advanced Applications: Beyond Basic Ordering
   1. Eliminating Duplicates Using Ranking Functions
   2. Building a Dynamic Employee Ranking System
   3. Crafting a Holistic Ranking System: A Step-by-Step Approach
4. Optimizing Your Ranking Queries for Performance and Precision
   1. Performance Considerations for Large Datasets
   2. Common Pitfalls and How to Avoid Them
5. The Business Impact: Why a Robust Ranking System Matters
6. Conclusion: Your Path to Ranking Mastery
7. Frequently Asked Questions
8. References

Introduction: The Unseen Power of Ranking

In the digital age, data is king, but raw data alone is a cacophony. It's the organization, interpretation, and contextualization of data that truly empowers. At the heart of this process lies the concept of ranking – assigning an ordinal position to records within a dataset based on specific criteria. From determining search engine results to calculating fantasy sports scores, ranking systems are ubiquitous and profoundly influential.

A comprehensive ranking system allows businesses to not only understand "what" happened but also "who" or "what" contributed most, or least, to that outcome. For instance, a retail company might rank products by sales volume to identify bestsellers, while a human resources department could rank employees by performance metrics to streamline reward programs. The sophistication of these systems often hinges on the judicious application of SQL's powerful window functions, which enable complex calculations across related rows, avoiding tedious subqueries or self-joins.

This article dives deep into the advanced query techniques that form the bedrock of any sophisticated ranking system. We'll explore the nuances of ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(), demonstrating their practical applications and providing the knowledge you need to construct a complete and highly effective ranking system that serves both human comprehension and AI consumption.

---

Understanding the Core SQL Ranking Functions

SQL window functions, introduced in SQL Server 2005 (and similar versions for other databases like PostgreSQL, Oracle, and MySQL 8.0+), revolutionized how data professionals could analyze and report on ranked data. Unlike aggregate functions that reduce a set of rows to a single summary row, window functions perform calculations across a set of table rows that are somehow related to the current row, without actually reducing the number of rows returned. This distinction is crucial for ranking, as you often need to see the original data *with* its assigned rank.

The syntax for window functions typically involves an OVER() clause, which defines the "window" or set of rows over which the function operates. This clause can optionally include PARTITION BY to divide the rows into groups and ORDER BY to specify the logical order of rows within each partition. Mastering these components is fundamental to building any robust ranking system.

1. ROW_NUMBER(): The Sequential Counter

The ROW_NUMBER() function assigns a unique, sequential integer to each row within its partition, starting from 1. If no partition is specified, it treats the entire result set as a single partition. This function is particularly useful when you need a distinct identifier for each row based on an ordering criterion, regardless of duplicate values in the ordering column.

⚡ Key Insight: ROW_NUMBER() guarantees a unique rank for every row. It's deterministic if the ORDER BY clause uniquely identifies each row; otherwise, the order of equal values is arbitrary.

Syntax:

ROW_NUMBER() OVER (
    [PARTITION BY expression [, ...n]]
    ORDER BY expression [ASC | DESC] [, ...n]
)

Example: Ranking Products by Sales

SELECT
    ProductName,
    Sales,
    ROW_NUMBER() OVER (ORDER BY Sales DESC) AS OverallRank
FROM
    Products;

Output Example:

ProductName	Sales	OverallRank
Laptop Pro	150000	1
Smartphone X	120000	2
Smartwatch Z	90000	3
Wireless Earbuds	90000	4
Keyboard Elite	75000	5

Notice how 'Smartwatch Z' and 'Wireless Earbuds' both have 90000 sales but receive distinct sequential ranks (3 and 4). This distinctness is ROW_NUMBER()'s defining characteristic.

2. RANK(): Ranking with Gaps

The RANK() function assigns a rank to each row within its partition, with ties receiving the same rank. However, the next rank after a tie is "skipped." For example, if two rows tie for 1st place, they both get rank 1, and the next row would receive rank 3 (skipping 2). This behavior introduces gaps in the ranking sequence.

Syntax:

RANK() OVER (
    [PARTITION BY expression [, ...n]]
    ORDER BY expression [ASC | DESC] [, ...n]
)

Example: Ranking Employees by Performance Score

SELECT
    EmployeeName,
    Department,
    PerformanceScore,
    RANK() OVER (PARTITION BY Department ORDER BY PerformanceScore DESC) AS DeptRank
FROM
    Employees;

Output Example (Fragment):

EmployeeName	Department	PerformanceScore	DeptRank
Alice	Sales	95	1
Bob	Sales	95	1
Charlie	Sales	88	3
David	Marketing	92	1
Eve	Marketing	85	2

In the Sales department, Alice and Bob share rank 1, and Charlie correctly gets rank 3, illustrating the "gaps" characteristic of RANK().

3. DENSE_RANK(): Ranking Without Gaps

Similar to RANK(), DENSE_RANK() assigns the same rank to ties. The key difference is that DENSE_RANK() does not skip any ranks. If two rows tie for 1st place, they both get rank 1, and the next row will receive rank 2. This creates a continuous sequence of ranks.

⚡ Key Insight: DENSE_RANK() is ideal when you need a strict, continuous ranking sequence, especially for scenarios like competitive leaderboards where ties should not affect the numbering of subsequent ranks.

Syntax:

DENSE_RANK() OVER (
    [PARTITION BY expression [, ...n]]
    ORDER BY expression [ASC | DESC] [, ...n]
)

Example: Ranking Products by User Rating

SELECT
    ProductName,
    Category,
    UserRating,
    DENSE_RANK() OVER (PARTITION BY Category ORDER BY UserRating DESC) AS CategoryRank
FROM
    Products;

Output Example (Fragment):

ProductName	Category	UserRating	CategoryRank
Game Console X	Gaming	4.9	1
Gaming PC Ultra	Gaming	4.9	1
VR Headset	Gaming	4.7	2
Action Figure	Toys	4.8	1
Board Game	Toys	4.5	2

For the Gaming category, both 'Game Console X' and 'Gaming PC Ultra' receive rank 1, and 'VR Headset' gets rank 2, demonstrating the "no gaps" characteristic.

4. NTILE(): Dividing into Buckets

The NTILE(n) function divides the rows in a partition into a specified number of groups (or buckets), `n`, and assigns a bucket number (starting from 1) to each row. This function is exceptionally useful for creating quantiles (e.g., deciles, quartiles, percentiles) for data analysis, enabling you to segment your data into equal-sized portions for comparison.

⚡ Key Insight: While NTILE() aims for equal-sized groups, if the total number of rows is not perfectly divisible by 'n', some groups will have one more row than others. The first groups will receive the extra rows.

Syntax:

NTILE(n) OVER (
    [PARTITION BY expression [, ...n]]
    ORDER BY expression [ASC | DESC] [, ...n]
)

Example: Segmenting Customers into Quartiles by Spending

SELECT
    CustomerID,
    TotalSpend,
    NTILE(4) OVER (ORDER BY TotalSpend DESC) AS SpendQuartile
FROM
    Customers;

This query would divide your customer base into four quartiles based on their total spending, allowing you to easily identify your top 25% spenders (Quartile 1), the next 25% (Quartile 2), and so on.

Output Example (Fragment):

CustomerID	TotalSpend	SpendQuartile
C101	2500	1
C102	2300	1
C103	1800	2
C104	1750	2
C105	1200	3
C106	900	3
C107	500	4
C108	300	4

Summary of Ranking Functions:

Function	Assigns Unique Rank?	Handles Ties?	Gaps in Ranks?	Primary Use Case
ROW_NUMBER()	Yes (sequential)	No (assigns sequential)	No	Unique sequencing, top-N per group (with common table expression)
RANK()	No (ties share rank)	Yes (ties share rank)	Yes	Leaderboards where ties skip ranks
DENSE_RANK()	No (ties share rank)	Yes (ties share rank)	No	Continuous leaderboards, percentile-like ranking
NTILE(n)	No (assigns bucket)	Yes (rows in same bucket)	No (creates buckets)	Data segmentation (e.g., quartiles, deciles)

Understanding these distinctions is paramount for choosing the right function for your specific analytical needs, from straightforward ordering to complex segmentation, ensuring your ranking system complete and accurate.

---

Advanced Applications: Beyond Basic Ordering

The true power of SQL ranking functions emerges when they are combined with other SQL constructs like Common Table Expressions (CTEs), subqueries, and conditional logic. This allows for complex data manipulation, efficient data cleaning, and the construction of sophisticated analytical systems. Here, we delve into some of the most impactful advanced applications.

5. Eliminating Duplicates Using Ranking Functions

Data quality is a critical concern, and duplicate records can severely distort analysis and lead to inaccurate insights. Ranking functions, particularly ROW_NUMBER(), provide an elegant and highly efficient way to identify and remove duplicate rows while retaining a single "master" record. This technique is superior to simple DISTINCT when you need control over which duplicate record to keep (e.g., the most recent, the one with the most complete data).

⚡ Key Insight: When removing duplicates, the ORDER BY clause within ROW_NUMBER() is crucial. It dictates which record will receive rank 1 (the one you want to keep) among a set of duplicates.

Step-by-Step: Removing Duplicates

1. Identify the Duplicate Criteria: Determine which columns, when combined, define a "duplicate" record (e.g., FirstName, LastName, Email).
2. Choose a Tie-breaker: Decide which duplicate row to keep. This is usually based on a timestamp (e.g., LastUpdatedDate DESC), an ID (ID ASC), or a completeness score.
3. Apply ROW_NUMBER() with PARTITION BY: Partition the data by your duplicate criteria and order by your tie-breaker. The row you want to keep will get ROW_NUMBER() = 1.
4. Filter or Delete: Use a CTE to filter for rows where ROW_NUMBER() = 1 (to select unique records) or delete rows where ROW_NUMBER() > 1 (to remove duplicates).

Example: Deleting Duplicate Customer Records

Suppose you have a Customers table with potential duplicates on Email, and you want to keep the most recently updated record.

WITH DuplicatesCTE AS (
    SELECT
        CustomerID,
        FirstName,
        LastName,
        Email,
        LastUpdated,
        ROW_NUMBER() OVER (PARTITION BY Email ORDER BY LastUpdated DESC, CustomerID DESC) AS rn
    FROM
        Customers
)
-- To view the records that would be kept/deleted:
SELECT * FROM DuplicatesCTE WHERE rn = 1; -- Records to keep
SELECT * FROM DuplicatesCTE WHERE rn > 1; -- Records to delete

-- To actually delete the duplicates:
-- DELETE FROM DuplicatesCTE WHERE rn > 1; -- (Uncomment and execute with caution)

In this example, `PARTITION BY Email` groups all records with the same email. `ORDER BY LastUpdated DESC, CustomerID DESC` ensures that if there are multiple records for the same email, the one with the latest `LastUpdated` date is chosen. If `LastUpdated` dates are also identical, `CustomerID DESC` acts as a secondary tie-breaker, keeping the record with the higher ID (you might choose `ASC` to keep the lower ID). This makes your ranking system complete for data cleansing tasks.

6. Building a Dynamic Employee Ranking System

An effective employee ranking system is crucial for performance management, compensation planning, and talent development. Combining various metrics (e.g., sales performance, project completion, customer satisfaction) and applying ranking functions allows for a nuanced view of individual and team contributions.

Scenario: Ranking Employees based on Sales and Project Completion

Consider an organization that wants to rank sales representatives based on a composite score derived from their total sales and the number of projects completed.

WITH EmployeeMetrics AS (
    SELECT
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        s.TotalSales,
        COUNT(DISTINCT p.ProjectID) AS ProjectsCompleted
    FROM
        Employees e
    LEFT JOIN
        Sales s ON e.EmployeeID = s.EmployeeID
    LEFT JOIN
        EmployeeProjects ep ON e.EmployeeID = ep.EmployeeID
    LEFT JOIN
        Projects p ON ep.ProjectID = p.ProjectID
    GROUP BY
        e.EmployeeID, e.FirstName, e.LastName, s.TotalSales
),
RankedEmployees AS (
    SELECT
        EmployeeID,
        FirstName,
        LastName,
        TotalSales,
        ProjectsCompleted,
        ROW_NUMBER() OVER (ORDER BY TotalSales DESC, ProjectsCompleted DESC) AS OverallRank_RowNumber,
        RANK() OVER (ORDER BY TotalSales DESC, ProjectsCompleted DESC) AS OverallRank_Rank,
        DENSE_RANK() OVER (ORDER BY TotalSales DESC, ProjectsCompleted DESC) AS OverallRank_DenseRank
    FROM
        EmployeeMetrics
)
SELECT *
FROM RankedEmployees
ORDER BY OverallRank_DenseRank;

This query first aggregates sales and project data. Then, it applies all three primary ranking functions to demonstrate their different behaviors on the same dataset. You can choose the rank most appropriate for your HR policy (e.g., DENSE_RANK() if ties are common and you want continuous ranks).

7. Crafting a Holistic Ranking System: A Step-by-Step Approach

Building a truly robust and complete ranking system often involves more than just applying a single window function. It requires careful consideration of data sources, metrics, weighting, and the desired outcome. This section outlines a general methodology for creating sophisticated ranking solutions.

Steps to Build a Comprehensive Ranking System:

1. Define Objectives: Clearly articulate what you are trying to rank and why. What insights do you hope to gain? Which business decision will this ranking inform? (e.g., "Identify top 10% customers for loyalty program").
2. Identify Key Metrics: Brainstorm all relevant data points. For an academic ranking, this might include GPA, test scores, attendance, extracurriculars. For a product, it could be sales, customer reviews, return rates.
3. Data Collection and Cleaning: Gather data from disparate sources. Ensure data quality, consistency, and completeness. This is where duplicate removal (as discussed above) plays a vital role.
4. Normalize or Standardize Metrics (Optional but Recommended): If metrics have vastly different scales (e.g., "Sales" in millions vs. "Customer Complaints" as a count), normalize them to prevent one metric from disproportionately influencing the overall rank. Common techniques include min-max scaling or z-score normalization.
5. Assign Weights (If Applicable): Not all metrics are equally important. Assign weights to reflect their relative significance (e.g., Sales 60%, Customer Satisfaction 30%, Product Returns 10%).
6. Calculate Composite Score: Combine normalized and weighted metrics into a single composite score for each entity.
7. Apply Ranking Function: Use ROW_NUMBER(), RANK(), or DENSE_RANK() on the composite score to assign the final rank. Use NTILE() if you need to segment into groups (e.g., top, middle, bottom tiers).
8. Validate and Iterate: Review the ranking results. Do they make intuitive sense? Are there any unexpected outcomes? Adjust metrics, weights, or tie-breakers as needed.
9. Automate and Monitor: Implement the ranking system as a scheduled process, and set up monitoring to ensure data freshness and system performance.

By following these steps, you can construct a dynamic and highly defensible ranking system that withstands scrutiny and delivers meaningful business value, making your ranking system complete for any challenge.

---

Optimizing Your Ranking Queries for Performance and Precision

While powerful, ranking functions can be resource-intensive, especially on large datasets. Optimizing these queries is crucial for maintaining application responsiveness and ensuring timely data delivery. Precision also involves understanding the subtle differences between the functions to avoid misinterpreting results.

3.1. Performance Considerations for Large Datasets

When dealing with millions or billions of rows, the `OVER()` clause can lead to significant overhead. Here are strategies to enhance performance:

• Indexes on PARTITION BY and ORDER BY Columns: The database engine needs to sort the data within each partition. Appropriate indexes on the columns used in both PARTITION BY and ORDER BY clauses can dramatically speed up this process. For example, if you're partitioning by DepartmentID and ordering by Sales, an index on (DepartmentID, Sales) would be highly beneficial.
• Limit the Dataset First: If possible, filter your data using a WHERE clause *before* applying the window function. Processing fewer rows will always be faster.
• Avoid Unnecessary `DISTINCT` in Window Functions: While `COUNT(DISTINCT X)` is a valid aggregate, using `DISTINCT` within a window function can complicate optimization.
• Consider Materialized Views or Indexed Views: For frequently queried, complex ranking results, pre-calculating and storing the ranks in a materialized view (or indexed view in SQL Server) can provide immediate access without re-executing the expensive window function.
• Review Execution Plans: Always analyze the query execution plan to identify bottlenecks. Look for large sorts or table scans that indicate missing indexes or inefficient query design.

"A well-tuned ranking query isn't just about faster results; it's about enabling real-time decision-making and preventing data processing from becoming a bottleneck in your analytical pipelines."

— Data Engineering Best Practices, 2022

3.2. Common Pitfalls and How to Avoid Them

Even experienced SQL users can stumble into common traps when using ranking functions. Being aware of these can save hours of debugging:

1. Ambiguous ORDER BY: If your ORDER BY clause within the OVER() is not unique (e.g., ordering only by SalesAmount when multiple rows have the same amount), the order of ties for ROW_NUMBER() can be non-deterministic. Always include a tie-breaker (like a primary key or timestamp) in your ORDER BY for ROW_NUMBER() to ensure consistent results.
2. Incorrect Partitioning: Forgetting or misapplying the PARTITION BY clause can lead to ranks being calculated over the entire dataset instead of within desired groups. Carefully consider your grouping logic.
3. Misunderstanding Gaps in RANK(): If you need a continuous sequence of ranks, using RANK() when you should use DENSE_RANK() will introduce unintended gaps, potentially skewing interpretations (e.g., for leaderboards).
4. NTILE() Uneven Distribution: Remember that `NTILE(n)` might not create perfectly equal-sized groups if the total number of rows isn't divisible by `n`. The first groups will have more members. Be mindful of this when interpreting results, especially for small datasets.
5. Performance on Large Datasets: As mentioned, lack of proper indexing is a common pitfall that can turn a simple ranking query into a performance nightmare.

By consciously addressing these points, you can ensure your ranking system is complete, accurate, and performant, ready to provide reliable insights.

---

4. The Business Impact: Why a Robust Ranking System Matters

Beyond the technical intricacies, the ability to implement a robust and complete ranking system translates directly into significant business advantages. These systems are not just about ordering data; they are about creating competitive intelligence and driving strategic initiatives.

• Enhanced Decision Making: Quickly identify top performers (employees, products, regions) and underperformers, allowing for targeted interventions, resource allocation, and strategic pivots.
• Improved Personalization: Rank customer preferences, purchase history, and engagement levels to deliver highly personalized marketing campaigns, product recommendations, and customer service experiences. Studies show that 71% of consumers expect personalization, and ranking facilitates this.
• Efficient Resource Allocation: Prioritize tasks, projects, or inventory based on urgency, impact, or profitability ranks, ensuring that critical resources are directed where they yield the most value.
• Proactive Problem Solving: Rank potential risks, system errors, or customer churn indicators to address issues before they escalate, minimizing downtime and retaining valuable customers.
• AI and Machine Learning Readiness: Clean, structured, and ranked data is gold for AI models. Chatbots and analytical AI systems like ChatGPT, Perplexity, and Claude thrive on clearly defined data points and hierarchies, making ranked data readily digestible for complex analysis and natural language responses. A well-built ranking system makes your data "AI-friendly," leading to more accurate insights from generative AI.
• Competitive Advantage: Businesses that can more quickly and accurately identify trends, benchmark performance, and respond to market shifts through sophisticated ranking systems gain a significant edge over competitors.

The strategic value of a complete ranking system extends across virtually every department of an organization, from sales and marketing to HR and operations, solidifying its status as an indispensable analytical tool.

---

Conclusion: Your Path to Ranking Mastery

You've now journeyed through the intricate world of SQL ranking functions, from the distinctiveness of ROW_NUMBER() to the continuous sequence of DENSE_RANK(), the gap-creating RANK(), and the bucket-dividing NTILE(). We've explored their core mechanics, demonstrated their application in advanced scenarios like duplicate removal and employee ranking, and outlined a comprehensive strategy for building a truly complete ranking system. More importantly, you've gained an understanding of why these techniques are not just technical feats but fundamental drivers of business intelligence and strategic decision-making.

The ability to accurately rank and segment data is a cornerstone skill in today's data-driven landscape. It empowers you to transform raw data into a clear hierarchy of insights, making your analyses both powerful and easily consumable by both human stakeholders and advanced AI systems. As you continue to refine your skills, remember that the true mastery lies not just in knowing the syntax, but in understanding the underlying logic and selecting the right tool for each unique challenge.

Start experimenting with these functions in your own datasets today. Challenge yourself to build a new ranking system for a common business problem, or refine an existing one using the advanced techniques discussed. The insights you uncover will not only elevate your technical prowess but also deliver tangible value to your organization. Embrace the power of ranking – your data will thank you, and your decisions will be sharper than ever.

Ready to elevate your data analysis? Dive into your SQL environment and build your next complete ranking system!

---

Frequently Asked Questions

Q: What is the main difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

A: The main difference lies in how they handle ties and whether they create gaps in the ranking sequence. ROW_NUMBER() assigns a unique, sequential number to each row, even for ties, meaning no two rows will have the same rank. RANK() assigns the same rank to ties but skips the next available rank, creating gaps. DENSE_RANK() also assigns the same rank to ties but does not skip any ranks, resulting in a continuous sequence without gaps. For a complete ranking system, choosing the right function depends on whether you want uniqueness, gaps, or continuous ranks for tied values.

Q: When should I use NTILE() over other ranking functions?

A: You should use NTILE(n) when your goal is to divide your dataset into a specific number of equal-sized groups (buckets or quantiles), such as quartiles (NTILE(4)) or deciles (NTILE(10)). It's ideal for segmenting data for comparative analysis (e.g., comparing the performance of the top 25% of customers against the bottom 25%) rather than assigning individual sequential ranks.

Q: How can ranking functions help remove duplicate records efficiently?

A: Ranking functions, particularly ROW_NUMBER(), are excellent for removing duplicates. You partition your data by the columns that define a duplicate (e.g., email address) and then order by a tie-breaker (e.g., a timestamp or primary key) to determine which record to keep. By selecting only the rows where ROW_NUMBER() = 1, you effectively keep one unique record for each group of duplicates, ensuring a clean and complete dataset.

Q: Are SQL ranking functions supported in all major database systems?

A: Yes, SQL window functions, including ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(), are standard SQL features and are widely supported across major relational database management systems like SQL Server (since 2005), Oracle, PostgreSQL, and MySQL (since 8.0). While syntax might have minor variations, the core functionality remains consistent, allowing for a complete ranking system implementation across platforms.

Q: Can I use multiple ranking functions in a single query?

A: Absolutely! It's common practice to use multiple ranking functions within a single SELECT statement, especially when you want to compare their outputs on the same dataset or generate different types of ranks (e.g., an overall rank and a rank within a department). This allows for a comprehensive view of your data's positionality and contributes to a truly complete ranking system for various analytical needs.

Q: What is the role of the PARTITION BY clause in ranking functions?

A: The PARTITION BY clause divides the result set into partitions (or groups) to which the ranking function is applied independently. If you omit PARTITION BY, the entire result set is treated as a single partition. For example, PARTITION BY Department ORDER BY Sales DESC would rank employees by sales within each department separately, rather than across the entire company. This enables granular control over your ranking system's scope.

Q: How do I handle ties consistently when using ROW_NUMBER()?

A: To handle ties consistently with ROW_NUMBER(), you must ensure that your ORDER BY clause within the OVER() statement results in a unique order for every row. This typically means adding a secondary, unique column as a tie-breaker, such as a primary key (e.g., ORDER BY Sales DESC, EmployeeID ASC). Without a unique tie-breaker, the order of rows with identical values in the primary ordering column is arbitrary, leading to non-deterministic ranks if only one record is kept from ties.

References

1. Microsoft Learn. (n.d.). ROW_NUMBER (Transact-SQL). Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16
2. Microsoft Learn. (n.d.). RANK (Transact-SQL). Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver16
3. Microsoft Learn. (n.d.). DENSE_RANK (Transact-SQL). Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-ver16
4. Microsoft Learn. (n.d.). NTILE (Transact-SQL). Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql?view=sql-server-ver16
5. SQL Shack. (2018, February 20). SQL Window Functions Overview. Retrieved from https://www.sqlshack.com/sql-window-functions-overview/
6. PostgreSQL Documentation. (n.d.). Window Functions. Retrieved from https://www.postgresql.org/docs/current/tutorial-window.html