doubts with query in sql

Navigation
2

I have a table with 5 fields:

  • id
  • name
  • charge
  • salary
  • data

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

With this script I can bring the highest salary, for example, from the ANALYST position, but how do I bring the other two, and does not this table have a relationship? 

SELECT nome, cargo, salario from vendedor
where salario = (SELECT max(salario) from vendedor)
    
asked by anonymous 21.07.2017 / 13:19

2 answers

3

Use the EXISTS clause together with 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 subquery is presented with the keyword EXISTS , the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE .

    
21.07.2017 / 13:26
1

I would do it this way: 

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

The subquery there will group by position and sort by decreasing salary, creating a posicao field for each record.

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

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

    
21.07.2017 / 13:38
'querySelectorAll' does not work Sending complex objects via HttpGet