Postgresql result filtering from lower one column value

Asked

Viewed 152 times

0

It is a matter of logic and knowledge of Postgresql resources. I have the following result of a query:

resultado da query

I need to do a filtering, grouping by [hour] so that only the nearest [real time] remains and, consequently, the whole row corresponding (I have a series of other columns).

I started trying to solve by calculating the absolute value of the difference in seconds. However, grouping by hour and calculating the minimum value of the [dif_abs] column, how to ensure that the other values of the corresponding row remain? Since I cannot use an aggregator, it might not correspond in some cases.

sql database postgresql

  • In this case, should only the line with dif_abs 45 remain? Put the expected result and with more values if possible.

  • Murillo, got with DISTINCT ON. I will post the reply. Thank you!

1 answer

0

I got it from DISTINCT ON.

In Select first Row in each GROUP BY group? , I saw a solution to this.

I can sort by [time] and [dif_abs] and extract the first distinct line by id (which is not in the example table I showed, but I have in my query; or we can even create with row_number() OVER ()). Thus:

SELECT DISTINCT ON (id_hora) id_hora, hora, hora_real, dif_abs FROM qry 
ORDER BY id_hora ASC, dif_abs ASC

This way, I will have the first line with a unique id where the [real time] is closer to the [time].

Browser other questions tagged sql database postgresql

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