Schedule Meeting


MariaDB: WITH TIES syntax

by | Dec 13, 2021 | MariaDB, MariaDB Features

MariaDB implemented the WITH TIES syntax in version 10.6. This feature may not be a ground breaker, but it is useful in some cases and it increases compatibility with some other DBMSs. Let’s see what it does, and what use cases it has.

Some history

MariaDB (just like MySQL) traditionally supported a PostgreSQL-like syntax LIMIT ... OFFSET, which in real cases is usually ORDER BY ... LIMIT ... [OFFSET ...]. See the MariaDB KnowledgeBase page. The use of OFFSET is often an antipattern, but this is a subject for another time.

In MariaDB 10.6, support for an alternative syntax OFFSET ... ROWS FETCH FIRST ... ROWS was added. Funnily enough, it was apparently added for compatibility with PostgreSQL, but this syntax is supported by Oracle and other DBMSs, too. And Oracle compatibility seems to be very important for MariaDB,

This work was MDEV-23908, and WITH TIES was added in a different task that I can’t easily find.

What WITH TIES does

Explaining WITH TIES with an example is incredibly easier by using an example, rather than trying to explain its meaning in English.

As mentioned, normally you use FETCH ... ROWS after ORDER BY. For example:

SELECT id, title
    FROM book
    ORDER BY author

This query orders books by title, and returns the first 10 of them.

The WITH TIES version is:

SELECT id, title
    FROM book
    ORDER BY author

This query could return more than 10 rows. After sorting the rows, it will return the first ten rows, plus the rows having the same author as the 10th row.

Syntax notes

You can use ROW and ROWS interchangeably, if you want. In the next examples I’m going to use the singular/plural in correct English, but you don’t have to.

You can specify ONLY instead of WITH TIES. It’s an equivalent for dear old LIMIT. If you’re a developer you might find it useful if your application has to choose whether to use ONLY or WITH TIES based on a condition.

NEXT isn’t really supported. If you use it, it will work exactly like FIRST.

Optimiser notes

WITH TIES doesn’t have any performance drawback (unless the number of additional returned rows is big enough to cause problems, in which case you simply shouldn’t use WITH TIES).

Currently EXPLAIN will always return the same estimated rows number, regardless whether WITH TIES is used or not:

> EXPLAIN SELECT name FROM test_data2 ORDER BY name
*************************** 1. row ***************************
         rows: 50
> EXPLAIN SELECT name FROM test_data2 ORDER BY name
*************************** 1. row ***************************
         rows: 50

WITH TIES use cases

Here are some practical use cases for WITH TIES.

Get all rows with the minimum or maximum value

Getting the row containing the maximum value is easy:

SELECT id, email
    FROM user
    ORDER BY registration_date DESC
    LIMIT 1;

But if there are several users that could match this condition, only one of them will be returned. To SELECT them all:

SELECT id, email
    FROM user
    ORDER BY registration_date DESC

Without WITH TIES, the query would be something like this, and it would be slower that necessary:

SELECT id, email
    FROM user
    WHERE registration_date = (
        SELECT MAX(registration_date) FROM user

Aggregating data in the application

DBMSs are very good at aggregating data (GROUP BY). And they have the aggregate functions that you normally want to use: minimum, maximum, arithmetic mean…

But what is you want to use a function that is not implemented, like the harmonic mean? Well, MariaDB supports stored aggregate functions, which means that you can implement the harmonic mean by yourself, in procedural SQL. Normally that would be the way to go, for example because it can avoid a lot of data roundtrip and lock time. But many teams don’t use procedural SQL for various reasons.

The way to emulate GROUP BY in an application would be something like this (pseudocode):

previousMonth = null
results = run_query(
        'SELECT month, cost FROM purchase WHERE year = 2021;'
series = [ ]
while (row = results.fetch_row()):
    if previousMonth == row.month:
        series = [ ]

This is not a sensible use of harmonic means. Please focus on the logic only.

The problem here is that selecting all the rows at once could lead to terrible performance. We need to limit the number of rows we get at once, but the number of rows to get is probably different for every month.

Again, we can use WITH TIES:

SELECT month, cost
    FROM purchase
    WHERE year = 2021 AND month = 1
    ORDER BY year, month

Loop over non-continuous, non-unique values

In the simplest case, to loop over rows based on one column, you’d do something like this:

loop i in range(1, 100):
    run_query('SELECT id, full_name FROM employee WHERE category_id = ' + str(i))

But how would you do the same kind of loop over a videogame table, with based on the score column? The values would theoretically be anything from zero to a high number, with many holes in the middle, and the same scores may occur many times.

Again, we’ll use WITH TIES:

SELECT player, score FROM game WHERE score > @last_score FETCH FIRST 1 ROW WITH TIES;

WITH TIES bugs in MariaDB

MariaDB doesn’t have any relevant open bugs involving WITH TIES. If such bugs are reported in the future, I will try to keep this page up to date.

Last checked: 2021-12-12.


We discussed what the WITH TIES syntax does, and a couple of use cases. If you have more use cases, please drop a comment. See the WITH TIES bugs in MariaDB section if you notice any wrong / weird behaviour.

To master advanced SQL and query optimisation, consider our SQL optimisation training for teams.

Federico Razzoli

Did you like this article?

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions:

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

MariaDB Catalogs: some use cases

MariaDB Catalogs: some use cases

This month I attended Monty’s talk about Catalogs at MariaDB Server Fest 2023 in Helsinki. He described this nice feature, which is still under development, and how it will work. But when he talked about the use cases, I was under the impression that catalogs have...

MariaDB/MySQL: working with storage engines

MariaDB/MySQL: working with storage engines

MariaDB and MySQL support several storage engines. See MariaDB and MySQL storage engines: an overview for a discussion about existing MariaDB and MySQL storage engines. Here we'll see how to work with them. We'll see how to obtain information about storage engines,...

MariaDB and MySQL storage engines: an overview

MariaDB and MySQL storage engines: an overview

MySQL was the first DBMS to introduce the concept of storage engines in the early 2000s. This was one of its main features. Later, MariaDB extended the storage engine API and included some storage engine maintained by third parties as part of its official...



Submit a Comment

Your email address will not be published. Required fields are marked *