How to set limit value dynamic?

I'm using BI Tool which has an option to write SQL Queries. I have a tables like

Table Name - Analysis

Module	Value
Lead	5
Deal	3

Table Name - Leads

module_name	Name	created_date
Lead	AAA	03-02-2021
Lead	BBB	03-02-2021
Lead	CCC	03-02-2021
Lead	FFF	03-02-2021
Lead	MMM	03-02-2021
Lead	ooo	02-02-2021
Lead	ppp	02-02-2021

Table Name - Deals

module_name	Name	created_date
Deal	DDD	03-02-2021
Deal	GGG	03-02-2021
Deal	EEE	03-02-2021
Deal	QQQ	03-02-2021
Deal	SSS	02-02-2021
Deal	TTT	02-02-2021

The above values are not static values, they will get updated on an hourly basis.

I have to use those values and set the limit for no of rows to display based on the latest created date

I have to get output as

Module	lead_name
Lead	AAA
Lead	BBB
Lead	CCC
Lead	FFF
Lead	MMM
Deal	DDD
Deal	GGG
Deal	EEE

I tried the following

SELECT module_name as module,
    lead_name as name
    from Leads 
    left join Analysis on Leads.module_name=Analysis.Module
    Order by Leads.Created_Date DESC
    LIMIT to_integer(Analysis.Value)
 UNION
 SELECT Deals.module_name as module,
    Deals.deal_name as name
    from Deals
    left join Analysis on Deals.module_name=Analysis.Module
    Order by Deals.Created_Date DESC
    LIMIT to_integer(Analysis.Value)

Is it possible to set the limit dynamically?

Thanks

Solution

I would suggest using row_number():

select l.module_name, l.Name, l.created_date
from (select l.*,
             row_number() over (order by created_date desc) as seqnum
      from leads l
     ) l
where seqnum < (select a.value from analysis a where a.module = 'Lead')
union all
select d.module_name, d.Name, d.created_date
from (select d.*,
             row_number() over (order by created_date desc) as seqnum
      from deals d
     ) d
where seqnum < (select a.value from analysis a where a.module = 'Deal');

Note: Use UNION ALL. UNION incurs overhead for removing duplicate values. And there appear to be no duplicate values in your data.

Also, the LEFT JOIN is superfluous. Presumably, you would get no rows if there were no matches in analysis.