NTILE Groups: How Uneven Rows and Ties Land in Each Tile

NTILE groups your sorted rows into the tiles you ask for, but the groups aren’t always the same size. You pick the number of tiles, and NTILE does the counting. This post shows how uneven rows and ties land, and how to pull the top 2 percent.

The Sample Table

Ten hiking trails, each with a length in miles. Two trails tie at 3.0 miles. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Hikes (Trail VARCHAR(20) NOT NULL, Miles DECIMAL(3,1) NOT NULL);
INSERT INTO Hikes (Trail, Miles) VALUES
  ('Ridge Loop', 2.0), ('Creek Path', 2.5), ('Cedar Walk', 3.0), ('Fern Trail', 3.0), ('Pine Circuit', 4.5),
  ('Lake Edge', 5.0), ('Falls Run', 5.5), ('Meadow Way', 6.0), ('Summit Climb', 8.0), ('Canyon Rim', 9.5);
GO

Four Tiles From Ten Rows

NTILE works like the other ranking functions. The ORDER BY inside OVER ( ) sorts the rows. The number inside NTILE says how many tiles you want. Here you want four, so each trail gets a quarter.

SELECT Trail, Miles, NTILE(4) OVER (ORDER BY Miles, Trail) AS Quarter
FROM Hikes
ORDER BY Miles, Trail;

Quarter 1 holds the three shortest trails: Ridge Loop, Creek Path and Cedar Walk. Quarter 4 holds the two longest. Ten rows don’t divide into four equal groups, so the sizes tell the story.

WITH Tiled AS (
  SELECT Trail, NTILE(4) OVER (ORDER BY Miles, Trail) AS Quarter FROM Hikes
)
SELECT Quarter, COUNT(*) AS Trails FROM Tiled GROUP BY Quarter ORDER BY Quarter;

The groups hold 3, 3, 2 and 2 trails. When the rows don’t divide evenly, the extra rows go to the first tiles, one each. No tile ever differs from another by more than one row.

The sort order decides which trails land in tile 1. Sort by Miles descending, and tile 1 becomes the upper quarter: the longest trails.

SELECT Trail, Miles, NTILE(4) OVER (ORDER BY Miles DESC, Trail) AS Quarter
FROM Hikes
ORDER BY Miles DESC, Trail;

Quarter 1 now holds Canyon Rim, Summit Climb and Meadow Way. The tile numbers follow the sort, so tile 1 is always the first slice of the list.

Two Tiles and Too Many Tiles

Ask for two tiles, and ten rows split into halves of five. Ask for more tiles than rows, and each row gets its own tile.

SELECT Trail, Miles, NTILE(2) OVER (ORDER BY Miles, Trail) AS Half
FROM Hikes
ORDER BY Miles, Trail;

SELECT Trail, NTILE(12) OVER (ORDER BY Miles, Trail) AS Tile FROM Hikes ORDER BY Miles, Trail;

The first query puts five trails in half 1 and five in half 2. The second numbers the trails 1 to 10, and tiles 11 and 12 stay empty.

Ties Don’t Stay Together

NTILE counts rows, not values. Cedar Walk and Fern Trail both walk 3.0 miles, but a tile boundary falls between positions 3 and 4. The two columns below use opposite tiebreakers.

WITH Tiled AS (
  SELECT Trail, Miles,
    NTILE(4) OVER (ORDER BY Miles, Trail) AS TiebreakerAscending,
    NTILE(4) OVER (ORDER BY Miles, Trail DESC) AS TiebreakerDescending
  FROM Hikes
)
SELECT Trail, Miles, TiebreakerAscending, TiebreakerDescending FROM Tiled WHERE Miles = 3.0 ORDER BY Trail;

With Trail in ascending order, Cedar Walk lands in quarter 1 and Fern Trail in quarter 2. Flip the tiebreaker, and they swap. Without a tiebreaker, nothing decides which tied row goes first, so the split isn’t guaranteed.

The Top 2 Percent

For the top N percent of a list, ask for 100 divided by N tiles and keep tile 1. The top 2 percent needs 50 tiles. This sample has 100 trail logs with a different number of minutes each, so each tile holds two rows. GENERATE_SERIES needs SQL Server 2022 or later.

CREATE TABLE TrailLog (LogID INT NOT NULL, Minutes INT NOT NULL);
INSERT INTO TrailLog (LogID, Minutes)
SELECT value, 100 + (value * 37) % 101 FROM GENERATE_SERIES(1, 100);
GO

WITH Tiled AS (
  SELECT LogID, Minutes, NTILE(50) OVER (ORDER BY Minutes DESC) AS Tile FROM TrailLog
)
SELECT LogID, Minutes, Tile FROM Tiled WHERE Tile = 1 ORDER BY Minutes DESC;

Tile 1 holds the two longest logs, 200 and 199 minutes. The shortcut TOP (2) PERCENT returns the same rows here.

SELECT TOP (2) PERCENT LogID, Minutes FROM TrailLog ORDER BY Minutes DESC;

NTILE is the better choice when you need every row’s tile, not only the top one.

When to Use NTILE

NTILE answers one question: which quarter is this row in? Use it to split work into batches, or to grade rows into bands such as the upper half. It says nothing about distance. Two trails in tile 1 can differ by a mile or by a tenth of a mile.

If you need cut points based on the values themselves, use a CASE expression or PERCENTILE_CONT instead.

Clean Up

Remove the sample tables when you finish.

DROP TABLE TrailLog;
DROP TABLE Hikes;

What to Remember

NTILE(n) cuts a sorted list into n tiles. The extra rows go to the first tiles. Tied rows can land in different tiles, so add a tiebreaker to the ORDER BY.

To go further, read Splitting Work Into Equal-Sized Batches With NTILE, Quartiles With PERCENTILE_CONT and NTILE and Data Binning in T-SQL: Fixed Width, NTILE, CASE, DATE_BUCKET.

A tile is not a value range, it is a share of the row count.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.