stored procedure ( inner join from same table)

Question

i have a table WEATHER_STATION.Precipitacao like this

value | date | station with various values  for example :

valor_observado  |    data_observacao    | estacao 
14                   2020-01-18              X
15                   2020-01-19              X
16                   2020-01-20              X
1                    2020-01-18              Y
2                    2020-01-19              Y
3                    2020-01-20              Y

i want to make a stored procedure with a date as input , that give me the highest value, the date and the station . In this example , the result with a input of '2020-01-18' will be

data_observacao | valor_observado | estacao
  2020-01-18           14            X 

I make a stored procedure like this but it didn't work (nothing appears) :

CREATE PROC PRECIPITACAO_MAXIMA_DATA @data date  AS
select data_observacao=@data , maximo_precipitacao,estacao
from WEATHER_STATION.Precipitacao 
inner join
(select data_observacao= @data ,max(valor_observado) as maximo_precipitacao 
from WEATHER_STATION.Precipitacao) a
on a.data_observacao = WEATHER_STATION.Precipitacao.data_observacao AND a.maximo_precipitacao = valor_observado 
GO

the result is this:

data_observacao | valor_observado | estacao

Answer 1

Consider using a row-limiting query:

create proc precipitacao_maxima_data 
    @data date  
as
    select top (1) t.*
    from weather_station.precipitacao t
    where data_observacao = @data
    order by valor_observacao desc;

This simply selects all rows whose data_observacao matches the given @data parameter, sorts them by descending valor_observacao, and retains the first row only.