Doubt with the Function window in Postgresql

Asked

Viewed 118 times

1

I have a job for college and I have a question in an exercise that I have to use the window functions to make the correct query.

This is my query:

SELECT pais, modelo, fabricante.nome, (venda.valor - automovel.preco) AS lucro,
       ROW_NUMBER() OVER(PARTITION BY pais ORDER BY pais, (venda.valor - automovel.preco))
FROM automovel, venda, fabricante 
WHERE automovel.codigo = venda.automovel AND fabricante.codigo = automovel.fabricante;

That’s the way out:

inserir a descrição da imagem aqui

The problem I’m having is that I should limit to appearing only the 2 cars of each of the most profitable countries, but I’m not getting. I searched on the RANGE | ROWS BETWEEN and so on but I couldn’t use it.

Thanks in advance for the help.

sql database postgresql

  • SELECT * FROM ( SELECT parents, model, manufacturer.name, (sale.value - car.price) AS profit, ROW_NUMBER() OVER(PARTITION BY parents ORDER BY parents, (sale.value - car.price)) AS positions FROM car, sale, manufacturer WHERE automovel.codigo = venda.automovel AND fabricante.codigo = automovel.fabricante) WHERE position <= 2;

  • Thank you @Filipel.Constante. It worked fine.

  • I put as answer, if possible mark as correct. This can help other users who may have this same question.

1 answer

0


Use it this way it’ll work:

SELECT * FROM ( SELECT pais, modelo, fabricante.nome, (venda.valor - automovel.preco) AS lucro, ROW_NUMBER() OVER(PARTITION BY pais ORDER BY pais, (venda.valor - automovel.preco)) AS posicao FROM automovel, venda, fabricante WHERE automovel.codigo = venda.automovel AND fabricante.codigo = automovel.fabricante) WHERE posicao <= 2;

Browser other questions tagged sql database postgresql

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