Home SQL Row Number: How to Order Rows by Country?

Databases

SQL Row Number: How to Order Rows by Country?

Learn how to use SQL ROW_NUMBER() to order table rows by country, putting US time zones first in alphabetical order.

byDev Solutions

January 18, 2026

SQL ROW_NUMBER function thumbnail emphasizing prioritizing USA rows first when ordering data by country in queries

⚙️ SQL's ROW_NUMBER() function gives each row a unique number based on how you want them ordered.
🇺🇸 You can put specific countries, like the "United States," first by using CASE inside the ORDER BY part.
📊 More than 65% of big business programs use complex SQL queries. This means using ROW_NUMBER() well is very important.
⛔ If you do not include an ORDER BY in the OVER() clause, rows often get numbers in a random way.
🔄 When you use PARTITION BY with ROW_NUMBER(), it lets you rank items within groups. This helps with deeper analysis.

Using SQL ROW_NUMBER to Order Rows by Country (USA First)

Sometimes you need to show country data in a specific order. For example, you might want to list "United States" first, and then all other countries alphabetically. SQL's ROW_NUMBER() function is a very helpful tool for this. In this guide, we will show you how to use the ROW_NUMBER() function. We will also explain how it is different from similar ranking functions, and how to use it to order, divide, and filter your data well.

What Is SQL ROW_NUMBER()?

SQL ROW_NUMBER() is a window function. It gives a unique number in order to each row in a set of results. This number is always the same for the same data. And it changes based on the ORDER BY clause you put inside the function's OVER() clause. Auto-numbering in a table saves identifiers. But ROW_NUMBER() is not saved. Instead, your query makes it each time it runs.

This function is very useful for tasks like:

Assigning line numbers in reports
Setting up pages in dashboards
Ranking rows based on your own rules
Picking the top N records for each group

It works by looking at the results in a certain order and giving numbers that start from 1.

Basic ROW_NUMBER Syntax

SELECT 
  ROW_NUMBER() OVER (ORDER BY column_name) AS row_num,
  *
FROM table_name;

In this simple example, every row in the table_name is assigned a number based on the ordering of column_name.

Understanding ROW_NUMBER() vs RANK() vs DENSE_RANK()

It is easy to confuse ranking functions, especially if you are new to them. Here is a simple explanation:

ROW_NUMBER(): Always gives a unique number to each row. Even if values are the same, they get different numbers.
RANK(): Gives the same rank to tied values, but then skips a number.
DENSE_RANK(): Also gives the same rank to ties, but does not skip any numbers.

Side-by-Side Example

SELECT 
  employee_name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Assume the salaries are: 100k, 100k, 90k.

employee_name	salary	row_num	rank	dense_rank
Alice	100000	1	1	1
Bob	100000	2	1	1
Carol	90000	3	3	2

Notice how row_num still gives unique numbers to all rows, while RANK() and DENSE_RANK() act differently when values are tied.

Custom Ordering With ROW_NUMBER()

The main benefit of ROW_NUMBER() comes from how you can change the ORDER BY clause inside the OVER() function. By changing the order of rows, you control how numbers are given out.

Alphabetical Ordering

SELECT 
  country,
  ROW_NUMBER() OVER (ORDER BY country) AS row_num
FROM countries;

This example numbers all countries alphabetically. But this does not help if your business needs mean certain countries (like the USA) must come first in the results.

Prioritizing Specific Countries Like 'USA' First

If your reports or user interface needs mean showing the United States, Canada, or any other countries at the top, SQL lets you sort conditionally. You do this by adding specific rules into the ORDER BY clause using the CASE expression.

Example Query: USA First

SELECT 
  country,
  ROW_NUMBER() OVER (
    ORDER BY 
      CASE WHEN country = 'United States' THEN 0 ELSE 1 END,
      country
  ) AS row_num
FROM countries;

Breakdown of Logic

CASE WHEN country = 'United States' THEN 0 ELSE 1 END: This puts the USA in the top spot when sorting.
The number from the CASE statement orders the data.
Countries with value 0 come before 1. So, USA appears first.
The second ordering, country, makes sure all other countries still appear in alphabetical order.

Extending to Multiple Priorities

You can use this idea to put multiple countries first:

SELECT 
  country,
  ROW_NUMBER() OVER (
    ORDER BY 
      CASE 
        WHEN country = 'United States' THEN 0
        WHEN country = 'Canada' THEN 1
        ELSE 2
      END,
      country
  ) AS row_num
FROM countries;

This ranking system puts the United States first, then Canada, and then all other countries.

Displaying Customer Data With Priority by Country

Let us use this idea in a real situation. Imagine putting your North American customers first in a dashboard.

Query for Prioritized Customer Listing

SELECT 
  customer_id, name, country,
  ROW_NUMBER() OVER (
    ORDER BY 
      CASE 
        WHEN country = 'United States' THEN 0
        WHEN country = 'Canada' THEN 1
        ELSE 2
      END,
      country
  ) AS country_row
FROM customers;

When you sort customers this way, you make your query results match your business goals or regional rules.

Using ROW_NUMBER for Pagination and Segmentation

Web applications often show data across different pages. ROW_NUMBER() is a common tool to do this.

Paginated Query Example

WITH NumberedOrders AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * 
FROM NumberedOrders
WHERE rn BETWEEN 11 AND 20;

This example shows the second "page" of orders. We assume you are showing 10 records on each page.

Key Tips for Pagination

Always keep the ORDER BY clause the same, or results may change when you go from one page to another.
Index the columns you use for ordering to make it faster.
Use ROW_NUMBER() only when you need unique numbers for each row. You might use OFFSET/FETCH for easier page setup in some database systems.

Selecting Top-N Rows per Group With PARTITION BY

ROW_NUMBER() becomes even more useful when you use it with the PARTITION BY clause. You can give numbers to rows inside groups.

Get Latest Order Per Customer

WITH RankedOrders AS (
  SELECT 
    customer_id,
    order_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * 
FROM RankedOrders
WHERE rn = 1;

This query gives you each customer's most recent order. This is great for dashboards, reports, or CRM systems.

Optimizing ROW_NUMBER() for Performance on Large Data

When working with large amounts of data, think about these ways to make it faster:

🔍 Index the columns used in ORDER BY — This makes sorting faster.
🎯 Limit columns in SELECT — do not use SELECT *; get only what you need.
🔄 Divide your data well — use partitions to reduce the amount of work the computer needs to do.
⏳ Batch hard queries — You might run big tasks when the system is not busy, or do them in small steps over time.

📈 Rob & Coronel (2007) say that over 65% of big business programs use a lot of SQL in real-time. So, making them run fast is very important.

Avoiding Common Mistakes With ROW_NUMBER()

SQL analysts and developers can run into several common problems when working with ROW_NUMBER():

❌ Using ROW_NUMBER() without ORDER BY leads to random results.
❌ Bad CASE logic in ORDER BY can order rows incorrectly.
❌ Failing to test for NULL values. Different database systems handle them in different ways.

Pro Tip

Check results using test data that has ties or null values. And then make sure the order is what you expect.

Integrating ROW_NUMBER With Other SQL Features

ROW_NUMBER() is very flexible and works well with:

PARTITION BY for Group-Specific Rankings

SELECT 
  department,
  employee_name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

This gives a salary rank from highest to lowest within each department.

CTEs to Simplify Complex Queries

WITH CountryRanks AS (
  SELECT 
    country,
    ROW_NUMBER() OVER (ORDER BY country) AS row_num
  FROM countries
)
SELECT * FROM CountryRanks
WHERE row_num <= 5;

CTEs allow for SQL blocks that are easy to read and work with. This makes them easier to understand and care for.

Advanced ROW_NUMBER Usage: Multi-Level Sorting

Sometimes you need to sort in many steps. For example, you might put the country first, then the city, and then other numbers.

Advanced Query Example

SELECT 
  office_id, country, city, rating,
  ROW_NUMBER() OVER (
    ORDER BY 
      CASE WHEN country = 'United States' THEN 0 ELSE 1 END,
      country,
      city,
      rating DESC
  ) AS office_rank
FROM offices;

This does the following:

U.S. offices appear first
Then by country alphabetically
Then by city
Then by highest rating

This can be very helpful in maps of the world or when looking at data by region in business intelligence tools.

Verify Your ROW_NUMBER Logic With Testing

Before using queries in a live system, set up ways to check your data:

✅ Add sample rows with expected results, especially for unusual cases.
✅ Use temporary tables during development to see how numbers are given.
✅ Use automatic SQL tests if you are using stored procedures.

Good testing makes sure your rows are numbered correctly. This is true especially when showing ordered results in important programs.

Try This Challenge

Let us put what you have learned to use. Write a query that ranks employees so that:

🇺🇸 USA entries appear first
🇨🇦 Canada entries appear next
🌍 All others follow
Results are alphabetized within each priority

Sample Solution

SELECT 
  employee_id, name, country,
  ROW_NUMBER() OVER (
    ORDER BY 
      CASE 
        WHEN country = 'United States' THEN 0
        WHEN country = 'Canada' THEN 1
        ELSE 2
      END,
      country
  ) AS row_position
FROM employees;

Optional: Add a second sort on department or salary to make your ranking rules stronger.

What’s Next?

Learn more about SQL tools with other helpful window functions:

NTILE(n): Puts rows into 'n' groups, like percentiles.
LAG() / LEAD(): Lets you see values from the row before or after to compare them.
RANK() & DENSE_RANK(): Rank items correctly even with ties.
Use ROW_NUMBER() inside stored procedures and views for row rules that you can use again.

Try out your queries in an SQL editor online. Or copy our open-source code from GitHub. This will help you start with ready-to-use data.