Filtering results in PostgreSQL from the lowest value of only one column

Question

Filtering results in PostgreSQL from the lowest value of only one column

Navigation

#1 by (0 votes)

0

It's a matter of logic and knowledge of PostgreSQL features. I have the following query result:

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

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

sql postgresql

asked by anonymous 15.05.2018 / 14:51

1 answer

How to pass the html script to css? Call to a member function change () on null

score 0 · Answer 1

I got 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 the id (which is not in the example table I showed, but I have in my query; So:

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

In this way, I will have the first line with unique id where the [real_time] is closest to [time].