2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / PostgreSQL3 / PostgreSQL 134 / PostgreSQL 13: LIMIT … WITH TIES
PostgreSQL 13: LIMIT … WITH TIES
Álvaro Herrera

PostgreSQL 13: LIMIT … WITH TIES

September 16, 2020/4 Comments/in 2ndQuadrant, Alvaro's PlanetPostgreSQL, PostgreSQL 13 /by Álvaro Herrera

One of the new features in PostgreSQL 13 is the SQL-standard WITH TIES clause to use with LIMIT — or, as the standard calls that, FETCH FIRST n ROWS. Thanks are due to Surafel Temesgen as initial patch author; Tomas Vondra and yours truly for some additional code fixes; and reviewers Andrew Gierth and Erik Rijkers. You can peruse the commit message.

[ T ] Maggie Taylor - A Caucus-Race (Alice in Wonderland) (2007)
A caucus race. An illustration by Maggie Taylor.

Ties are very frequently when ranking things; for instance, in a caucus race you could have many ties, and for sure you don’t want to deprive participants of their prizes! What WITH TIES does is pretty simple: it adds any following row or rows to your result set, if they rank equal to the last row returned per the LIMIT clause, according to the ORDER BY clause.

If you want just the two employees with the highest salary, you may do this:

SELECT * FROM employees
ORDER BY salary DESC LIMIT 2;
namesalarydepartment
Alicia1600engineering
Oruga1500marketing
“Oruga” is, of course, Spanish for “Caterpillar”.

So are you itching to know the salary of the next person? What if she matches Oruga, and was just left out by pure chance or bad luck? That can happen, as you well know; and fortunately, WITH TIES is now there to save the day. (Note that, in reality, we do not handle WITH TIES in the LIMIT clause as such.  You have to use the FETCH FIRST syntax, which is the standards-mandated one, in order to be able to use WITH TIES.)

SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 2 ROWS WITH TIES;
namesalarydepartment
Alicia1600engineering
Oruga1500sales
Conejo Blanco1500marketing
A mad tie party. John Tenniel

There! White Rabbit had to be listed, and now he is.

A couple notes before you go too mad. LIMIT (or more precisely FETCH FIRST) no longer promises to return exactly the number of rows you specify. You could get two or twenty additional rows, or 100x as many rows as you asked for. Among other things this means that you need to keep track of how many rows you’ve seen thus far, if you’re paginating results. In the above, you got three rows, so for the next page you skip that many by adding the right OFFSET clause:

SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 2 ROWS WITH TIES
OFFSET 3;
namesalarydepartment
Falsa Tortuga1400marketing
Duquesa1300sales
Liebre de Marzo1300engineering

We again got three rather than just two we asked for. So for the next page you’d have to skip six. And so on. Be sure to have enough thimbles for everybody.

The other thing to keep in mind is that you must make sure to only use the ORDER BY clause that suits the WITH TIES clause; if you wanted, say, to have the rows of the same salary ordered by name, you’d have to use a subquery. Otherwise, the distinction in names would solve the tie on salary, so the next row would not be included. For example:

SELECT * FROM (
       SELECT * FROM employees
       ORDER BY salary DESC
       FETCH FIRST 2 ROWS WITH TIES) AS subq
ORDER BY salary DESC, name;

This feature is there to help you show all rows that are of the same value — it lets you not discriminate against some rows of equal worth based solely on physical location within the table.

Happy paginating!

Tags: 2QLovesPG, postgresql 13
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
4 replies
  1. Lewis
    Lewis says:
    September 16, 2020 at 6:10 pm

    Couldn’t you have your cake and eat it already using group by with concatenation functions?

    Reply
  2. Purav
    Purav says:
    September 17, 2020 at 3:17 am

    Interesting feature. Super write-up. Thanks

    Reply
  3. Alexander Farber
    Alexander Farber says:
    September 17, 2020 at 9:56 pm

    Nice feature!

    Reply
  4. Michael Lewis
    Michael Lewis says:
    October 1, 2020 at 5:08 pm

    Awesome write up, and great that the PG team is continually implementing more of the standard.

    This same behavior is fairly simple to implement before PG13 with rank() in a subquery and where rank < X in the outer set.

    Reply

Trackbacks & Pingbacks

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

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

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
7 Best Practice Tips for PostgreSQL Bulk Data Loading 7 Best Practice Tips for PostgreSQL Bulk Data Loading New Features in PG 13 Webinar: New Features in PostgreSQL 13 [Follow Up]
Scroll to top
×