DEV Community

Cover image for FETCH FIRST … ROWS WITH TIES
Slava Rozhnev
Slava Rozhnev

Posted on

1

FETCH FIRST … ROWS WITH TIES

In this article, I want to show an innovative solution to a common problem. Find the records in the table with the maximum value in one of the columns.
For example, we will look for employees with the highest salary.

The standard solution is to find the maximum value in the salary column and then use that as a filter.

select * 
from employees
where salary = (select max(salary) from employees);
Enter fullscreen mode Exit fullscreen mode

try query on sqlize.online

However, the SQL:2008 standard offers a new solution to this problem: FETCH FIRST … ROWS WITH TIES

select * 
from employees
order by salary desc
fetch first 1 rows with ties;
Enter fullscreen mode Exit fullscreen mode

With the same success, using the FETCH FIRST … ROWS WITH TIES construction, the problem of finding N maximum and minimum values ​​is easily solved, the solution of which in other ways is no longer so trivial

https://sqlize.online/sql/psql13/50703dc61c4b65b38ffaa338bbe7f683/

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more