CODEWITHSUNDEEP
sqloracle-databasefunctionmax
kr_v
kr_v

Reputation: 139

Max function returning multiple values [SQL]

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?

Upvotes: 0

Views: 375

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

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;

Upvotes: 1

dani herrera
dani herrera

Reputation: 51655

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

Upvotes: 0

Related Questions