I’ve a PostgreSQL question:
SELECT DISTINCT ON ("contract"."contract_id") "contract"."id"
FROM "contract_versions" "contract"
WHERE "contract"."client_id" = 1
GROUP BY "contract"."contract_id", "contract"."id"
ORDER BY "contract"."contract_id", "contract"."change_effective_date" DESC
I need to add one thing like if contract_id happens greater than as soon as then change_effective_date >= now()
I attempted utilizing:
SELECT DISTINCT ON ("contract"."contract_id") "contract"."id",
COUNT("contract"."contract_id") AS cnt
FROM "contract_versions" "contract"
WHERE "contract"."client_id" = 1
AND CASE WHEN "cnt" > 1 THEN "contract"."change_effective_date" <= now() END
GROUP BY "contract"."contract_id", "contract"."id"
ORDER BY "contract"."contract_id", "contract"."change_effective_date" DESC
however its throwing an error column “cnt” doesn’t exist
Thanks