Max function returning multiple values [SQL]

Question

I have 3 tables: money, student, faculty. This query returns each faculty and highest stipend in each one of them.

select 
    f.name as "FACULTY_NAME",
    max(stipend) as "MAX_STIPEND"
from 
    money m, student s 
inner join
    faculty f on f.id_faculty = s.faculty_id
where 
    m.student_id = s.id_student
group by 
    f.id_faculty, f.name;

Query works fine:

FACULTY_NAME     |    MAX_STIPEND
-----------------+---------------
IT Faculty       |    50
Architecture     |    60
Journalism       |    40

However when I add s.name to original query to also show the name of the student who received max_stipend, query is not working like it used to - it returns all of the students

select 
    f.name as "FACULTY_NAME",s.name,
    max(stipend) as "MAX_STIPEND"
from 
    money m, student s 
inner join
    faculty f on f.id_faculty = s.faculty_id
where 
    m.student_id = s.id_student
group by 
    f.id_faculty, f.name, s.name;

Query result:

FACULTY_NAME    |   s.name  |   MAX_STIPEND
----------------+-----------+---------------
IT Faculty      |   Joe     |   50
IT Faculty      |   Lisa    |   10
Architecture    |   Bob     |   60
Journalism      |   Fred    |   5
Architecture    |   Susan   |   5
Journalism      |   Tom     |   40

It does the same thing using right, left and inner joins. Can someone tell where the problem is?

Pick one join syntax and stick with it, for starters. Preferably the one that uses the JOIN keyword. — Shawn
– Shawn, Commented Oct 14, 2018 at 19:54
You typically GROUP BY the columns you select, except those who are arguments to set functions. — jarlh
– jarlh, Commented Oct 14, 2018 at 19:57

Gordon Linoff · Accepted Answer · 2018-10-14 20:52:03Z

1

First, you should be using proper JOIN syntax for all your joins.

Second, you can use Oracle's keep syntax:

select f.name as FACULTY_NAME,
       max(stipend) as MAX_STIPEND,
       max(s.name) keep (dense_rank first order by stipend desc)
from money m join
     student s 
     on  m.student_id = s.id_student join
     faculty f
     on f.id_faculty = s.faculty_id   
group by f.id_faculty, f.name;

answered Oct 14, 2018 at 20:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dani herrera Over a year ago

all time learning from you @gordonLinoff. Nice answer. keep. nice.

dani herrera · Accepted Answer · 2018-10-14 20:13:56Z

However when I add s.name to original query to also show the name of the student who received max_stipend, query is not working like it used to - it returns all of the students

When you add s.name you are looking for min value for each user.

If you want the name of user who has the MAX_STIPEND you should to move to window functions. For example Dense Rank in MS SQL Server.

with cte as
(select 
    f.name as "FACULTY_NAME",
    s.name as "STUDENT_NAME",
    stipend as "MAX_STIPEND",
    DENSE_RANK() OVER   
    (PARTITION BY f.name, s.name ORDER BY i.stipend DESC) AS Rank  
 from 
    money m 
 inner join student s on m.student_id = s.id_student
 inner join
    faculty f on f.id_faculty = s.faculty_id
)
select "FACULTY_NAME", "STUDENT_NAME"
from cte
where rank = 1

Not all sql brands have windowed functions. Here the link for dense_rank on MySQL and also dense_Rank for Oracle

Collectives™ on Stack Overflow

Max function returning multiple values [SQL]

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related