mysql - Formula Query on PostgreSQL -

my input data this

wavelength   reflectance 341.6        1.15   343.1        1.14 344.7        1.13 346.3        1.14 347.9        1.14 349.5        1.12 351.1        1.12 352.6        1.13 354.2        1.13 

i using formula query

with cte as(     select        row_number() over(             partition cast(wavelength int)                      -cast(wavelength int)%5         order wavelength) row_id,       wavelength,       avg( reflectance ) over(         partition cast(wavelength int)                      -cast(wavelength int)%5         order wavelength         rows between 1 following              , unbounded following) reflectance               test     )     select trunc(wavelength/5)*5 wavelengthwavelength, reflectance     cte     row_id = 1 

in query povides output this

wavelength  reflectance 340         2.6400000000000000 340         2.5200000000000000 345         2.5200000000000000 355         2.5500000000000000 360         2.4250000000000000 365         2.4650000000000000 365         2.5450000000000000 370         2.4733333333333333 380         2.6600000000000000 385         2.7400000000000000 390         2.7700000000000000 390         2.8833333333333333 

in output 340,365,390 placed twice, instead of placed twice should placed once based on min value, how should this...

with cte as(    select row_number() over(partition wavelength::int - wavelength::int%5                             order wavelength) row_id,           wavelength,           avg(reflectance) over(partition wavelength::int - wavelength::int%5                            order wavelength                            rows between 1 following                            , unbounded following) reflectance       test    ) select distinct on (1)        trunc(wavelength/5)*5 wavelength, reflectance   cte  row_id = 1 order  1, 2; 

distinct on postgres extension standard sql disitnct , particularly useful if want add more columns , still pick row minimum reflectance. otherwise group by can job well.

more details in related answer:
select first row in each group group?

with distinct on, need order by in final select. however, want add either way. per documentation:

currently, window functions require presorted data, , query output ordered according 1 or of window functions' partition by/order by clauses. not recommended rely on this, however. use explicit top-level order by clause if want sure results sorted in particular way.