SELECT DISTINCT in PostgreSQL

The DISTINCT keyword is used to remove duplicate values from the result set, ensuring that only unique records are returned.

1. Using DISTINCT to Select Unique Rows

SELECT DISTINCT department FROM employees;

✅ Returns a list of unique departments from the employees table, with no duplicates.

2. Using DISTINCT with Multiple Columns

SELECT DISTINCT department, job_title FROM employees;

✅ Returns unique combinations of department and job title (e.g., it may return IT Manager, HR Assistant).

3. Using DISTINCT with ORDER BY

You can combine DISTINCT with ORDER BY to sort the unique records.

SELECT DISTINCT department FROM employees ORDER BY department ASC;

✅ Returns unique departments sorted in ascending order.

4. Using DISTINCT with Aggregate Functions

You can use DISTINCT within aggregate functions like COUNT().

SELECT COUNT(DISTINCT department) FROM employees;

✅ Returns the number of unique departments in the employees table.

5. Using DISTINCT ON for Specific Columns

DISTINCT ON is used when you want to select a unique row for a given set of columns and get the first row of each group based on sorting order.

SELECT DISTINCT ON (department) department, first_name, salaryFROM employeesORDER BY department, salary DESC;

✅ Returns the first employee in each department with the highest salary.

• Note: DISTINCT ON requires an ORDER BY clause to determine which row is returned for each group.

6. DISTINCT with Subqueries

SELECT DISTINCT department FROM (SELECT department FROM employees WHERE salary > 50000) AS subquery;

✅ Returns unique departments from employees with a salary greater than 50,000.

Performance Considerations

• Using DISTINCT on large datasets can be resource-intensive.
• Indexes on the columns used in DISTINCT can improve performance.