Thus the department appears first in the order by list. You must always use the DISTINCT ON expression as the first expression in the ORDER BY clause. I have added employee_id to the order by clause to return the employee with least employee_id in case there are more than 1 employees with same salary within a department. ) keeps only the first row of each set of rows where the given expressions evaluate to equal. If we describe the various columns, the DISTINCT clause will analyze the matching rows or data according to the grouping of these columns values. SELECT DISTINCT ON eliminates rows that match on all the specified expressions. The snippet from the Official documentation for PostgreSQL is as follows. SELECT DISTINCT eliminates duplicate rows from the result. You will receive “any” 1 employee from each department without the ‘salary desc’ clause. 2 Answers Sorted by: 57 While the previous answer appears correct, I don't feel that it is particularly clear. Without the ‘salary desc’ in the order by clause the result will be unpredictable. How does this work exactly It gives you distinct combinations of all the expression in the SELECT list. I have used ‘salary desc’ in the order by clause to return the employee with highest salary. SELECT DISTINCT Syntax SELECT DISTINCT column1, column2. Inside a table, a column often contains many duplicate values and sometimes you only want to list the different (distinct) values. ‘DISTINCT ON department’ will return only 1 row per department. The SELECT DISTINCT statement is used to return only distinct (different) values. Order by department,salary desc,employee_id employee_idīelow is the query to identify the employees with highest salary in each department. In Postgres database, you can use DISTINCT ON to select one row per group.Ĭonsider the below employee table where we have employee_id, department and salary fields.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |