Get whole row with MIN-Value and GROUPed BY another column

Question

i have a table like this:

>       from    to     time   changes 
>1      A       B      40     0
>2      A       C      30     2
>3      A       D      20     1
>4      E       B      10     0

I have some given values for 'to' - such as 'B' and 'C'. I need to get the whole row for MIN(time), GROUPed BY from...

...for instance:

>      from    min(time)     to  changes
>1     A       20            D   1
>2     E       10            B   0

Tried code like:

"SELECT min(t.time), t.changes, t.from, t.to
    FROM table t
    GROUP BY t.from"

and tried to solve the problem with INNER JOINs, but it seems to be a bit tricky to GROUP BY another column than the column used to aggregate.

Answer 1

Aggregate functions are not the best approach here, because each column is then done independently (so you can get the min(time) but cannot easily access the other columns on the row where that time value exists).

The most straightforward approach is to use distinct on, e.g.:

select distinct on(t.from) t.from, t.time, t.to, t.changes
from table t
order by t.from, t.time asc --ensures you get the row with the lowest t.time value for each value of t.from

This will give you one row for each from value and the corresponding column values from the record in which time is at a minimum for that from value