CODEWITHSUNDEEP
postgresql
Rajat
Rajat

Reputation: 121

Dynamically select rows and union them using postgresql

I have a question in postgresql; I've tried using a cursor for the following but could not get this to work.

I have a large employee table that has multiple columns, one of them being the company name, see below:

master table

The master table is not sorted by date, it needs to be sorted during the filtering process. This table can have more than 100,000 records; I want to create a 2nd table that will have only some of the records (by each company name) - essentially, get the rows for each company name by pre-defined number of records and then union the tables together into a new table. My pre-defined records table would look something like this

required records

Ideally, I could have more than 1000 companies in my original, master table and I might need only 50 companies and some records for the 50 companies that will be defined in my records table. How can this be done in postgresql

Sample Output: Records are the number of records that are mentioned in the pre-defined records desired table and are sorted by date

output

Upvotes: 1

Views: 235

Answers (1)

mukund
mukund

Reputation: 603

You can left join and filter the master table with the help of predefined number of records table. Then create a rank-ordered by date which can further nested and filtered using where condition.

select t3.* from (

    select 
        t1.* 
        , t2.number_of_records 
        , RANK() OVER (PARTITION BY t1.company_name ORDER BY t1.date_column asc) ranks
    from employee_table t1 
    left join pre_defined_records_table t2 
        on t1.company_name = t2.company_name
        where t1.company_name in (select company_name
    from pre_defined_records_table)
    
) t3
where t3.ranks <= t3.number_of_records

I have used the dummy names for your columns and tables as the sample data is not reproducible. PS - Check also Dense_Rank() function.

Upvotes: 1

Related Questions