SQL Standard

SQL NTILE

The SQL ntile function divides the rows of an ordered partition into a set of defined equal parts. Each part is assigned a number expression that ranges from 1.

In Standard SQL, the ntile function can be expressed as shown:

NTILE(const_int_expr)

The function will divide the rows into the defined const_int_expr parts based on the row order and returns a 1-based part number which is assigned to each row.

For more verbose and explicit syntax, we can express it as shown:

NTILE(parts) OVER (

[PARTITION BY partition_expression, ... ]

ORDER BY sort_expression [ASC | DESC], ...

)

The parts parameter defines the total number of parts in which the rows are divided. The value must be an integer or a query that returns a positive integer.

The partition by clause will define the expression that separates the rows into sets of sections. The ntile function is then applied to these partitions.

Order by will specify the order in which the rows are ordered. This is applied in each partition.

SQL Ntile – Practical Example

To illustrate using the ntile function, let us use a practical example. Start by creating a database to store the sample data.

CREATE DATABASE ntile_db;

Switch to the database and create a table as shown:

create table employees (

id serial primary key,

full_name varchar(255),

email varchar(255),

department varchar(100),

start_date date,

active bool,

category varchar(50)

);

NOTE: The above query is provided for PostgreSQL databases. Feel free to switch the query above to match your desired database engine.

Once you have the table schema defined, we can proceed and add sample data. Run the query below to add sample data.

The above adds sample data to the table. We will use this data to illustrate how to use the ntile function.

Example 1

Let’s use the ntile function to divide the above data into 3 separate rows based on the category.

select id,

full_name,

email,

department,

start_date,

active,

category,

ntile(3) over (partition by category order by id asc) as cat_rank

from employees;

The above query should partition the result by the category and apply the ntile function. The resulting table is shown below:

Closing

Using this article, we discovered how to what the ntile function does and how to use it. Check the documentation for your database engine to explore more.

Thanks for reading!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list