---
title: "How to assign rows to ranked groups in AWS Athena"
description: "How to use the NTILE function in Athena"
author: "Bartosz Mikulski"
author_bio: "Principal AI Engineer & MLOps Architect. I bridge the gap between \"it works in a notebook\" and \"it works for 200 million users.\""
author_url: https://mikulskibartosz.name
author_linkedin: https://www.linkedin.com/in/mikulskibartosz/
author_github: https://github.com/mikulskibartosz
canonical_url: https://mikulskibartosz.name/assign-rows-to-ranked-groups-aws-athena
---

A ranked group is a method of grouping rows in the following way:

* First, we have to order the rows by a column.
* After that, we determine how many rows should be in every group. All of the groups (except the last one) must have the same number of rows.
* In the end, we traverse the ordered rows and assign them to groups one by one.

For example, let's assume that I have numbers 3, 5, 1, 8, 2, 4, 7, 9, 0, and I want to assign them to three groups.

In the first step, I have to order them 0, 1, 2, 3, 4, 5, 7, 8, 9. I calculate the size of the group. I have nine numbers, and I want three groups, so every group contains three numbers. Finally, I assign the numbers to groups:

* Group I: 0, 1, 2
* Group II, 3, 4, 5
* Group III: 7, 8, 9

How do we do that in Athena? Fortunately, there is a built-in function NTILE:

```sql
SELECT t.*,,
       NTILE(3) OVER (ORDER BY col)
FROM table t;
```