additional condition in a SQL query with min(Date)

Question

I have the code presented below provided to me by one of the members but I do not understand how it acts:

select pdam.*, pr.*
from "DAMPay" as pdam left join lateral
     (select min(pr."AsigStart") as startt
      from "PolsRisc" as pr
      where pdam."PRID" = pr."Index"
     ) pr
     on true;

The code extracts the min of start from another table. I wanna add an aditional condition like:

where pdam."PAYDate" between '2020-06-01' and '2020-06-30'

I cannot insert the condition without an error. Also, I need only to see min(pr."AsigStart") from the pr table not all columns (pr.*). Thanks

What did you try, and which error did you get?

GMB
– GMB

2020-07-20 09:44:37 +00:00
Commented Jul 20, 2020 at 9:44 — GMB
– GMB, Commented Jul 20, 2020 at 9:44

GMB · Accepted Answer · 2020-07-20 09:45:48Z

1

I want to add an aditional condition like:

You would typically add a where clause to your existing query:

select pdam.*, pr.*
from "DAMPay" as pdam 
left join lateral (
    select min(pr."AsigStart") as startt
    from "PolsRisc" as pr
    where pdam."PRID" = pr."Index"
) pr on true
where pdam."PAYDate" between '2020-06-01' and '2020-06-30'

Also, I need only to see min(pr."AsigStart") from the pr table not all columns (pr.*)

That's what the query does already. In the outer query, pr refers to the subquery (not to table PolsRisc). The subquery returns a single column, called startt, so that's pr.* is actually equivalent to pr.startt.

answered Jul 20, 2020 at 9:45

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alin-Iustinian Toderita Over a year ago

thanks for the query @GMB. Now it works but it seems the returned value min(start) is not correct. But your explination is fine. Thanks

Collectives™ on Stack Overflow

additional condition in a SQL query with min(Date)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related