Get top row(s) with highest value, with ties

by

The first query fails if any row has quantity IS NULL (as Gordon demonstrates).
The second query only fails if all rows have quantity IS NULL. So it should be usable in most cases. (And it’s faster.)

Postgres 13 or newer

Use the standard SQL clause WITH TIES:

SELECT id
FROM   product
ORDER  BY quantity DESC NULLS LAST
FETCH  FIRST 1 ROWS WITH TIES;

db<>fiddle here

Works with any amount of NULL values.

The manual:

SQL:2008 introduced a different syntax to achieve the same result,
which PostgreSQL also supports. It is:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

In this syntax, the start or count value is required by the standard
to be a literal constant, a parameter, or a variable name; as a
PostgreSQL extension, other expressions are allowed, but will
generally need to be enclosed in parentheses to avoid ambiguity. If
count is omitted in a FETCH clause, it defaults to 1. The WITH TIES
option is used to return any additional rows that tie for the last
place in the result set according to the ORDER BY clause; ORDER BY is
mandatory in this case. ROW and ROWS as well as FIRST and NEXT are
noise words that don’t influence the effects of these clauses.

Notably, WITH TIES cannot be used with the (non-standard) short syntax LIMIT n.

It’s the fastest possible solution. Faster than either of your current queries. More important for performance: have an index on (quantity). Or a more specialized covering index to allow index-only scans (a bit faster, yet):

CREATE INDEX ON product (quantity DESC NULLS LAST) INCLUDE (id);

See:

We need NULLS LAST to keep NULL values last in descending order. See:

Postgres 12 or older

A NULL-safe query:

SELECT id, quantity
FROM   product
WHERE  quantity IS NOT DISTINCT FROM (SELECT MAX(quantity) FROM product);

Or, probably faster:

SELECT id, quantity
FROM  (
   SELECT *, rank() OVER (ORDER BY quantity DESC NULLS LAST) AS rnk
   FROM   product
   ) sub
WHERE  rnk = 1;

See:

Faster alternatives for big tables:

More Related Contents:

  1. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  2. How to return result of a SELECT inside a function in PostgreSQL?
  3. Possible to perform cross-database queries with PostgreSQL?
  4. Reset auto increment counter in postgres
  5. Optimize query with OFFSET on large table
  6. GROUP BY and aggregate sequential numeric values
  7. Safely rename tables using serial primary key columns
  8. How to use (install) dblink in PostgreSQL?
  9. get JOIN table as array of results with PostgreSQL/NodeJS
  10. updating table rows in postgres using subquery
  11. How can I send email from PostgreSQL trigger?
  12. Find overlapping date ranges in PostgreSQL
  13. How do you do date math that ignores the year?
  14. Postgres manually alter sequence
  15. What’s the proper index for querying structures in arrays in Postgres jsonb?
  16. “Operator does not exist: integer =?” when using Postgres
  17. How much disk-space is needed to store a NULL value using postgresql DB?
  18. Aggregate a single column in query with many columns
  19. Aggregating (x,y) coordinate point clouds in PostgreSQL
  20. PostgreSQL convert columns to rows? Transpose?
  21. Does Postgres support nested or autonomous transactions?
  22. PostgreSQL delete with inner join
  23. Regular expression in PostgreSQL LIKE clause
  24. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  25. Update a column of a table with a column of another table in PostgreSQL
  26. Check if NULL exists in Postgres array
  27. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. Temporal database design, with a twist (live vs draft rows)
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment