Only show the biggest value

[easyForYouMaybe]

I want to show the Client wich has the biggest price (RP.preco + RT.preco) to pay by year:

For example:

Client 1 for 2017 has to pay 200$
Client 2 for 2017 has to pay 199$
Client 3 for 2016 has to pay 200$

The output I wanted was:

Client 1 - 2017 - 200$
Client 3 - 2016 - 200$

What Im getting:

Client 1 - 2017 - 200$
Client 2 - 2017 - 99$
Client 3 - 2016 - 200$

SELECT DISTINCT C.name name, EXTRACT(YEAR FROM RP.date_hour) year, (RP.price + RT.price) Price
FROM CLIENTE C, REGISTODISPOSITIVO RD, VEICULO V, REGISTOTRADICIONAL RT, REGISTOPORTICOS RP
WHERE C.nif = RD.nif
AND C.ranking = ‘Gold’
AND RD.n_serieDispositivo = RP.n_SerieDispositivo
AND RD.n_serieDispositivo = RT.n_Seriedispositivo
ORDER BY Ano desc;

[r937]

four problems

1, you’re missing a join condition for the veiculo table

2, because of EXTRACT, i expect you really want GROUP BY with SUM for the prices – otherwise, as it is now, you’re going to get one result row for each date, not for each year

3, you forgot to mention which database system you’re using

4, you might be getting inflated results, depending on how many RP and RT rows exist for each RD

SELECT c.name , EXTRACT(YEAR FROM rp.date_hour) AS year , rp.price + rt.price AS price FROM cliente c INNER JOIN registodispositivo rd ON rd.nif = c.nif INNER JOIN veiculo v ON ???? INNER JOIN registotradicional rt ON rt.n_seriedispositivo = rd.n_seriedispositivo INNER JOIN registoporticos rp ON rp.n_seriedispositivo = rd.n_seriedispositivo WHERE c.ranking = 'gold' ORDER BY ano DESC;