doubt with query in sql

Asked

Viewed 50 times

2

I have a table with 5 fields:

  • id
  • name
  • office
  • salary
  • date

...and wanted to make a query that returns the name of the people with the highest salary, of each position, and I have for example 3 positions: analyst, dev and engineer.

With this script I can bring the highest salary for example from the ANALYST position, but how to bring from the other two, being that this table has no relationship?

SELECT nome, cargo, salario from vendedor
where salario = (SELECT max(salario) from vendedor)
sql sql-server group-by

2 answers

3


Use the clause EXISTS together with the NOT

SELECT *
  FROM vendedor t
 WHERE NOT EXISTS(SELECT 1
                    FROM vendedor t2
                   WHERE t2.cargo = t.cargo
                     AND t2.nome <> t.nome
                     AND t2.salario > t.salario);

EXISTS

When a sub-quota is displayed with the keyword EXISTS, the subconsultation acts as a test of existence. The clause WHERE of the external query tests whether the lines returned by the sub-query exist. The sub-query does not actually produce any data; it returns a value TRUE or FALSE.

  • what does this mean (select 1??) worked out the query changed the parameters of the column etc etc...

  • @Eversonsouzadearaujo o SELECT 1 is just to force some outcome to query be satisfied, since the result does not interfere in anything. The secret of the operation is the EXISTS. I’ll put a reference to it in the answer

  • @Eversonsouzadearaujo put in Sqlfiddle the query working together with another possible example: http://sqlfiddle.com/#! 6/afd0c/5

  • 1

    @Sorack thank you

1

I’d do it that way:

select * 
from (
    select *, posicao = row_number() over (partition by cargo order by salario desc) 
    from pessoas
    ) a
where posicao = 1

The subquery will then group by the post and order decreasingly by salary, creating a field posicao for each record.

In the outside query, I limit the result to only items with position 1.

If you wanted to bring the top 10 of each group, just use where posicao <= 10.

Browser other questions tagged sql sql-server group-by

You are not signed in. Login or sign up in order to post.