0

I am trying to join 2 tables like so:

left join (
    select t1.createdate, min(f1.createdate) as mindt, f1.status_aft
    from new_table t1
    left join new_folder f1 on t1.veh_id = f1.veh_id
    where f1.createdate > t1.createdate
    group by t1.createdate
) h3
on t1.createdate = h3.createdate
and f1.createdate = h3.mindt

But I am getting an error:

ERROR: column "f1.status_aft" must appear in the GROUP BY clause or be used in an aggregate function

This makes sense because I do not group it, my goal is just to take the value that is in that current row when f1.createdate is min.

For example:

A       B       C
one     10      a
one     15      b
two     20      c
two     25      d

Becomes

A       B       C
one     10      a
two     20      c

Because a and c was the values when column B were the lowest after grouping it by column A.

I've seen this answer but I still can't apply it to my scenario.

How can I achieve the desired result?

Jonas Palačionis
  • 4,591
  • 4
  • 22
  • 55

1 Answers1

0

my goal is just to take the value that is in that current row when f1.createdate is min.

If you want just one row, you can order by and limit:

left join (
    select t1.t1.createdate, f1.createdate as mindt, f1.status_aft
    from new_table t1
    left join new_folder f1 on t1.veh_id = f1.veh_id
    where f1.createdate > t1.createdate
    order by t1.createdate limit 1
) h3
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks, but that does not work for me, I get duplicated rows and `null` values in that column. Do you have an idea what might cause this? – Jonas Palačionis Dec 22 '20 at 16:22
  • 1
    @JonasPalačionis: the subquery is guaranteed to return just one row (or no row at all), so this cannot duplicate rows. You only showed one small part of the query, so I cannot comment on how to solve the issue you describe here... – GMB Dec 22 '20 at 21:36