FETCH FIRST … ROWS WITH TIES

#sql #postgres #analytics

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);

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;

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/