Postgres sql for Min Value-CodePudding

i have a postgres table like this:

|      val_date       |val_io_id|val_value|
2021-12-30 03:20:00 02  9224    30,3    
2021-12-30 11:10:00 02  9224    36,9
2021-12-30 11:35:00 02  9226    2,2
2021-12-30 12:50:00 02  6645    983
2021-12-30 11:20:00 02  3884    373
2021-12-30 12:45:00 02  3464    4,05
2021-12-30 15:50:00 02  9224    3912580
2021-12-30 17:45:00 02  5296    10,1

the only way i can makeit work is like this ... but i lose time part of occur from val_date

SELECT date(val_date), min(val_value)
FROM   data_store.vals_1
                WHERE val_io_id=9224 and date(val_date)='2021-12-30'
                Group BY date(val_date)

I want to extract min of (val_value) and datetime of occurr (val_date) for a val_io_id i want result like this:

|      val_date       |val_io_id|val_value|
2021-12-30 03:20:00 02  9224    30,3

thanks

CodePudding user response：

use row_number()

cte as 
(
select val_date,val_io_id,val_value, row_number()over(partition by val_io_id
order by val_value asc) as rnnmbur from data_store.vals_1
) select val_date,val_io_id,val_value where rnnmbur=1

another way in PostgreSQL

SELECT DISTINCT ON (val_io_id), val_date,val_value
FROM data_store.vals_1 a

ORDER BY a.val_value sac

CodePudding user response：

thankyou but no one working:

 cte as 
(
select val_date,val_io_id,val_value, row_number()over(partition by val_io_id
order by val_value asc) as rnnmbur from data_store.vals_1
WHERE val_io_id=9224 and date(val_date)='2021-12-30'
) select val_date,val_io_id,val_value where rnnmbur=1

ERROR:  syntax error at or near "cte"
LINE 1: cte as 
        ^

seccond:

SELECT DISTINCT ON (val_io_id) val_date, val_value
FROM data_store.vals_1 
WHERE val_io_id=9224 and date(val_date)='2021-12-30'
order by val_io_id  asc

it return first reccord of the day ... not min value