Shulou(Shulou.com)06/01 Report--

Usually in the data analysis of customer business, there is often a need to get the largest or smallest records of certain fields in a certain group.

For example, the employee table:

[local]: 5432 pg12@testdb=#\ d employee Table "public.employee" Column | Type | Collation | Nullable | Default-+-id | | integer | name | character varying (30) | department | character varying (30) | salary | double precision |

We generate test data through MockData, a total of 1000 rows, a total of 12 department.

[local]: 5432 pg12@testdb=# select count (*) from employee; count-1000 (1 row) Time: 22.747 ms [local]: 5432 pg12@testdb=# select distinct department from employee; department-- Marketing Training Sales Business Development Product Management Research and Development Support Legal Accounting Services Human Resources Engineering (12 rows) Time: 2.616 ms

Now you want to get the largest employee in the salary of each department.

The general practice is to use grouping to find the maximum / minimum values, and then make an association query:

[local]: 5432 pg12@testdb=# select a.* from employee a, (select department,max (salary) as salary from employee group by department) b pg12@testdb-# where a.department = b.department and a.salary = b.salary order by a.department Id | name | department | salary-+-+-- +-453 | Ericha Hendrikse | Accounting | 9958.5 | Kyle Hartegan | Business Development | 9754.93 969 | Odelinda Marsden | | Engineering | 9942.3 201 | Glen Kasperski | Human Resources | 9559.54 | Mirabelle Lesslie | Legal | 9720.49 214 | Chane Koschek | Marketing | 9943.86 371 | Josy Ayliff | Product Management | 9975.48 191 | Meir Alvaro | Research and Development | 9870 770 | Adoree de Guerre | Sales | 9808.65 | | Benoite Overlow | Services | 9884.79 866 | Shirlee McIlherran | Support | 9884.08 586 | Renae Jerromes | Training | 9904.24 (12 rows) Time: 8.256 ms [local]: 5432 pg12@testdb=#

The problem with this approach is that if max salary has more than one record, the above query will have multiple results.

PostgreSQL provides DISTINCT ON, which can be easily implemented.

[local]: 5432 pg12@testdb=# SELECT DISTINCT ON (department) pg12@testdb-# * pg12@testdb-# FROMpg12@testdb-# employeepg12@testdb-# ORDER BYpg12@testdb-# department,pg12@testdb-# salary DESC Id | name | department | salary-+-+-- +-453 | Ericha Hendrikse | Accounting | 9958.5 | Kyle Hartegan | Business Development | 9754.93 969 | Odelinda Marsden | | Engineering | 9942.3 201 | Glen Kasperski | Human Resources | 9559.54 | Mirabelle Lesslie | Legal | 9720.49 214 | Chane Koschek | Marketing | 9943.86 371 | Josy Ayliff | Product Management | 9975.48 191 | Meir Alvaro | Research and Development | 9870 770 | Adoree de Guerre | Sales | 9808.65 | | 9884.79 | Benoite Overlow | Services | 9884.79 | Shirlee McIlherran | Support | 9884.08 586 | Renae Jerromes | Training | 9904.24 (12 rows) Time: 11.445 ms

Excellent Feature!

references

The Many Faces of DISTINCT in PostgreSQL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.