CODEWITHSUNDEEP
sqlsql-servert-sql
0x6B6F77616C74
0x6B6F77616C74

Reputation: 2630

Select person with the highest salary in every city

enter image description here

Using database described by this model, I need to show table with the following columns:

cities.name | persons.first_name+'space'+persons.last_name AS [THE LUCKY MAN] | occupations.salary [the best salary]

It's a tricky question because there is no direct connection between entities "Cities" and "Occupations".

@UPDATE

No, it's not a homework. The closest answer that I can give:

SELECT cP.name, p.first_name+' '+p.last_name AS [The lucky man], T.[MAX] FROM cities cP
    JOIN persons p ON (p.id_city=cP.id_city)
    JOIN occupations o ON (p.id_persons=o.id_person)
    JOIN (SELECT MAX(ow.salary) AS [MAX], ow.id_person AS [idperson] FROM occupations ow GROUP BY ow.id_person) T ON (p.id_persons=T.[idperson])

Maybie this question is just confusing rather than tricky.

Upvotes: 0

Views: 129

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Edit - thanks for posting what you've tried so far. The issue is that when selecting the max you are also grouping by person. The max has to be selected when grouping only by city, and then those values (city and highest salary) have to be joined back to the original tables.

On a side note, in the query below, the join into the inline view/subquery should be an outer join if you want to show cities with no people (highest salary would be zero, technically).

select  c.name,
        p.first_name+'space'+p.last_name AS [THE LUCKY MAN],
        o.salary [the best salary]
from    persons p
   join cities c
     on p.id_city = c.id_sity
   join occupations o
     on p.id_person = o.id_person 
   join   ( select   p.id_city,
                     max(o.salary) as city_highest
            from     persons p
                join occupations o
                  on p.id_person = o.id_person
            group by p.id_city
          ) v
     on p.id_city = v.id_city
    and o.salary = v.city_highest

Upvotes: 3

Related Questions