PostgreSQL: How to Select Row with Closest Value

Often in PostgreSQL you may want to select a row from a table with the closest value to a specified value.

You can use the following syntax to do so:

SELECT *
FROM sales_data
ORDER BY abs(sales - 80) 
LIMIT 1;

This particular example selects the row from the sales_data table with the value in the sales column that is closest to 80.

Here is how this syntax works:

• First, we use SELECT * to select all rows from the sales_data table.
• Then, we use the ORDER BY clause to order the rows based on the absolute difference between the value in the sales column and the value 80.
• Lastly, we use LIMIT 1 to only return the first row, which is the row with the smallest absolute difference between the values.

The following example shows how to use this syntax in practice.

Example: How to Select Row with Closest Value in PostgreSQL

Suppose that we create the following table named sale_data that contains information about total sales made during various years at some company:

-- create table named 'sales'
CREATE TABLE sales_data (
  year INT,
  sales INT
);

-- add rows to the table 
INSERT INTO sales_data VALUES (2019, 54);
INSERT INTO sales_data VALUES (2020, 70);
INSERT INTO sales_data VALUES (2021, 77);
INSERT INTO sales_data VALUES (2022, 71);
INSERT INTO sales_data VALUES (2023, 90);
INSERT INTO sales_data VALUES (2024, 86);

-- view all rows in table
SELECT * FROM sales_data;

 year | sales 
------+-------
 2019 |    54
 2020 |    70
 2021 |    77
 2022 |    71
 2023 |    90
 2024 |    86

Suppose that we would like to select the row with the value in the sales column that is closest to the value 80.

We can use the following syntax to do so:

SELECT *
FROM sales_data
ORDER BY abs(sales - 80) 
LIMIT 1;

 year | sales 
------+-------
 2021 |    77

Notice that this returns the row with a value of 77 in the sales column, which represents the row with the closest value to 80.

You can use similar syntax to select the row with a value closest to a specified value in a column of your own table.

Note: If you would like to return the N closest rows then change LIMIT 1 to a different number. For example, you could use LIMIT 3 to return the rows with the 3 closest values to a specific value.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select First and Last Record from Query
PostgreSQL: Select Rows with Max Value in Column By Group
PostgreSQL: How to Find Maximum Value Across Multiple Columns