Group data based on higest value of a partcular column-CodePudding

 I have two tables department and category.In my query result listing same department multiple times.I want to group departments based on category table priority column.

 Eg Data:

 id    dept_name     dept_category  priority

 1      Cardio         category 2     2
 2      Ortho          category 3     3
 3      Ortho          category 1     1
 4      ENT            category 1     1
 5      Ortho          category 2     2

I wannt the reslt like:

 id    dept_name     dept_category  priority

 1      Cardio         category 2     2
 3      Ortho          category 1     1
 4      ENT            category 1     1

How can i fetch the resul like this.In my category table cantain priority 1,2,3 then 1 is highest priority.I need to group department based on highest priority.

CodePudding user response：

We can use DISTINCT ON with Postgres:

SELECT DISTINCT ON (dept_name) *
FROM yourTable
ORDER BY dept_name, priority;