Waiting for PostgreSQL 13 – Support FETCH FIRST WITH TIES

I have no idea how I missed that, but: if it wasn't for Alvaro's blog post I wouldn't know that: on 7th of April 2020, Alvaro Herrera committed patch:

Support FETCH FIRST WITH TIES
 
WITH TIES is an option to the FETCH FIRST N ROWS clause (the SQL
standard's spelling of LIMIT), where you additionally get rows that
compare equal to the last of those N rows by the columns in the
mandatory ORDER BY clause.
 
There was a proposal by Andrew Gierth to implement this functionality in
a more powerful way that would yield more features, but the other patch
had not been finished at this time, so we decided to use this one for
now in the spirit of incremental development.
 
Author: Surafel Temesgen <surafel3000@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Discussion: https://postgr.es/m/CALAY4q9ky7rD_A4vf=FVQvCGngm3LOes-ky0J6euMrg=_Se+ag@mail.gmail.com
Discussion: https://postgr.es/m/87o8wvz253.fsf@news-spur.riddles.org.uk

This is huge. Alvarro wrote about it, but let me just quickly make a simple test for you:

=$ CREATE TABLE users (
    id       INT generated BY DEFAULT AS IDENTITY PRIMARY KEY,
    some_val INT
);

Now, let's assume we have some rows with data like this:

=$ INSERT INTO users (some_val)
    SELECT 1 + CAST(random() * 4 AS INT) FROM generate_series(1,10)
    returning *;
 id | some_val 
----+----------
 41 |        2
 42 |        2
 43 |        1
 44 |        2
 45 |        5
 46 |        5
 47 |        1
 48 |        1
 49 |        4
 50 |        2
(10 ROWS)

Data sorted by some_val:

=$ SELECT * FROM users ORDER BY some_val;
 id | some_val 
----+----------
 43 |        1
 48 |        1
 47 |        1
 41 |        2
 42 |        2
 44 |        2
 50 |        2
 49 |        4
 45 |        5
 46 |        5
(10 ROWS)

Now, let's assume you want to get top five users with largest some_val, something like this:

=$ SELECT * FROM users ORDER BY some_val DESC LIMIT 5;
 id | some_val 
----+----------
 45 |        5
 46 |        5
 49 |        4
 50 |        2
 42 |        2
(5 ROWS)

Does this mean that 6th user has some_val below 2? No. It doesn't.

Now, thanks to the patch by Surafel, we can:

=$ SELECT * FROM users ORDER BY some_val DESC
    fetch FIRST 5 ROWS WITH ties;
 id | some_val 
----+----------
 45 |        5
 46 |        5
 49 |        4
 50 |        2
 42 |        2
 44 |        2
 41 |        2
(7 ROWS)

Or if I want to force previous default:

=$ SELECT * FROM users ORDER BY some_val DESC
    fetch FIRST 5 ROWS ONLY;
 id | some_val 
----+----------
 45 |        5
 46 |        5
 49 |        4
 50 |        2
 42 |        2
(5 ROWS)

This is great. To get this result before one would have to use recursive queries, or simply 2nd query to get potentially tied rows. Now – it's all in one, simple, single, query.

Thanks a lot to all involved.

One thought on “Waiting for PostgreSQL 13 – Support FETCH FIRST WITH TIES”

Comments are closed.