create view selecting the max in one column related with another column

Question

I need to create a view in PostgreSQL 9.4 about this table:

CREATE TABLE DOCTOR (
    Doc_Number INTEGER,
    Name    VARCHAR(50) NOT NULL,
    Specialty   VARCHAR(50) NOT NULL,
    Address VARCHAR(50) NOT NULL,
    City    VARCHAR(30) NOT NULL,
    Phone   VARCHAR(10) NOT NULL,
    Salary  DECIMAL(8,2) NOT NULL,
    DNI     VARCHAR(10) NOT NULL,
    CONSTRAINT pk_Doctor PRIMARY KEY (Doc_Number)
  );

The view will show the rank of the doctors with highest salary for each specialty, I tried this code but it shows all of the doctors fro each specialty:

CREATE VIEW top_specialty_doctors 
AS (Select MAX(Salary), name, specialty from DOCTOR
    where specialty = 'family and community'
    or specialty = 'psychiatry'
    or specialty = 'Rheumatology'
    group by name, salary, specialty);

How can I do for the view shows only the doctor with highest salary for each specialty.

You're grouping on `name` too. Each doctor will come back as their own group (unless you find some John Does). — Millie Smith, Apr 11 '15 at 19:42
but if I remove `name` from `group by` I get this error: ERROR: column "doctor.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: AS (Select MAX(Salary), name, specialty from DOCTOR ^ ********** Error ********** ERROR: column "doctor.name" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803 Character: 60 — alwayslearn, Apr 11 '15 at 19:47
Yeah. See w0lfs answer. You can't select a column that you didn't group by. If you have five rows in the group, how do you choose which name you should display? You need some sort of subselect. w0lf chose to use a common table expression. — Millie Smith, Apr 11 '15 at 19:49
Your explanation is ambiguous: `the rank of the doctors with highest salary for each specialty`. Please clarify, do you want to rank doctors (how?) or just the one(s) with the highest rank per specialty? What if multiple docs tie for the first rank? — Erwin Brandstetter, Apr 11 '15 at 21:45

score 3 · Accepted Answer · edited May 23 '17 at 11:43

DISTINCT ON is a simple Postgres specific technique to get one winner per group. Details:

Select first row in each GROUP BY group?

CREATE VIEW top_specialty_doctors AS 
SELECT DISTINCT ON (specialty)
       salary, name, specialty
FROM   doctor
WHERE  specialty IN ('family and community', 'psychiatry', 'Rheumatology')
ORDER  BY specialty, salary DESC, doc_number  -- as tiebreaker

And you do not need parentheses around the query for CREATE VIEW.

If multiple docs tie for the highest salary, the one with the smallest doc_number is selected.

If salary can be NULL, use DESC NULLS LAST:

PostgreSQL sort by datetime asc, null first?

For big tables and certain data distributions other query techniques are superior:

Optimize GROUP BY query to retrieve latest record per user

Thank you so much, this code works good! Finally I get the correct answer. — alwayslearn, Apr 11 '15 at 22:27

score 1 · Answer 2 · edited Apr 11 '15 at 19:49

1

Here's a query that shows the best doctor by salary for each of the specialties:

with specialty_ranks as (
  select 
    Salary, name, specialty,
    rank() over (
      partition by specialty
      order by salary desc
    ) as rank
  from DOCTOR
      where specialty in ('family and community', 'psychiatry', 'Rheumatology')
)
select specialty, name, salary
from specialty_ranks
where rank = 1;

The query uses a CTE and the RANK() window function to do the job. You might want to read their docs if you haven't used them before.

edited Apr 11 '15 at 19:49

answered Apr 11 '15 at 19:44

Cristian Lupascu

39,078
16
100
137

1

I don't want to re-edit your answer without knowing for sure, w0lf, but did you have the semicolon at the beginning so as not to cause problems with previous queries? I've found that some queries will not play nice before a CTE directly after them in T-SQL, so I will often put a semicolon exactly like you did to appease this. – Millie Smith Apr 11 '15 at 19:54
@MillieSmith Exactly; that's why I had the semicolon. Since OP seems to want to put the query directly into a view, the semicolon might not be needed. – Cristian Lupascu Apr 11 '15 at 19:56
Fair enough. I'll leave it be. – Millie Smith Apr 11 '15 at 19:58
The problem is that I can't use some of the comands you use, I need to do the function more similar to mine (creating a view). I've not studied comands like with, rank or over yet. There is some simpler way or quite similar to my code? – alwayslearn Apr 11 '15 at 20:03
@alwayslearn You can take my query and use it directly in your view, [like this](http://www.sqlfiddle.com/#!15/8f128/14). You could replace the `WITH` clause with a subselect, but I don't see a solution without `RANK()`. – Cristian Lupascu Apr 11 '15 at 20:07
I tried your code without changes at first and I get an empty table, if I do `select * from DOCTOR` gives me a full table therefore the insertions are correct, and all of the doctors have a salary and specialty, some idea about the problem? – alwayslearn Apr 11 '15 at 20:24
@w0lf Can't we just get rid of the CTE? `select sa, n, sp, rank() over (...) as r from doctor where sp in (...) and r = 1`. – Millie Smith Apr 11 '15 at 20:32
1

@MillieSmith: Postgres (like every other DBMS) needs the statement termination character where it belongs: at the **end**. That's why I removed it in the answer - it simply doesn't make sense in a Postgres answer. And [SQL Server needs it at the end as well](https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons). Sticking to the `;with` kludge simply encourages stupid things like this: http://stackoverflow.com/questions/23078215/cte-in-from-clause-of-sql-query because people actually think the keyword is `;with` not `with`. – Apr 11 '15 at 22:07
@alwayslearn can you reproduce the issue in the sqlfiddle link I posted above? – Cristian Lupascu Apr 12 '15 at 07:13
Your code didn't give me any error only returns an empty view, but with code of @Erwin Brandstetter works good – alwayslearn Apr 12 '15 at 13:50

score 0 · Answer 3 · answered Apr 15 '15 at 07:51

Without using Common Table Expressions or analytics, you can use an inline view/virtual table:

Create View top_specialty_doctors as
    Select  m.MaxSalary, d.Name, d.Specialty
    From    Doctor  d
    Join( -- Expose largest salary of each specialty
        Select  Specialty, Max( Salary) as MaxSalary
        From    Doctor
        Group by Specialty
    ) as m
        on  m.Specialty = d.Specialty
        and m.MaxSalary = d.Salary
    Where specialty in( 'family and community', 'psychiatry', 'Rheumatology' );

Using a CTE instead of the inline view makes the query more readable and allows the query optimizer to turn out better performance (usually). They are really easy to learn.

create view selecting the max in one column related with another column

3 Answers3