CODEWITHSUNDEEP
sqlnetezza
Adam12344
Adam12344

Reputation: 1053

Filling in Rows when Too Few - Netezza SQL

I have a table with Customer, Product, & Rank. The table is at a Customer-Product level and has up to 5 products for each customer. When there are less than 5 products I would like to fill in data from another table so there are 5.

Original Table:

| Customer | Product | Rank |
|----------|---------|------|
| 123456   | 456     | 1    |
| 123456   | 457     | 2    |
| 123456   | 458     | 3    |
| 234567   | 234     | 1    |
| 234567   | 235     | 2    |
| 234567   | 236     | 3    |
| 234567   | 237     | 4    |
| 234567   | 238     | 5    |
| 345678   | 712     | 1    |
| 345678   | 713     | 2    |

Fill in Table:

| Product | Rank |
|---------|------|
| 123     | 1    |
| 124     | 2    |
| 125     | 3    |
| 126     | 4    |
| 127     | 5    |

Results I'm Looking for:

| Customer | Product | Rank |
|----------|---------|------|
| 123456   | 456     | 1    |
| 123456   | 457     | 2    |
| 123456   | 458     | 3    |
| 123456   | 123     | 4    |
| 123456   | 124     | 5    |
| 234567   | 234     | 1    |
| 234567   | 235     | 2    |
| 234567   | 236     | 3    |
| 234567   | 237     | 4    |
| 234567   | 238     | 5    |
| 345678   | 712     | 1    |
| 345678   | 713     | 2    |
| 345678   | 123     | 3    |
| 345678   | 124     | 4    |
| 345678   | 125     | 5    |

edit: I should have mentioned I wanted the top ranking row to be inserted first. So, product 123 should be rank 4 for customer 123456

Upvotes: 1

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You can do this with insert . . . select:

insert into original(customer, product, rank)
    select c.customer, f.product, f.rank
    from (select distinct customer from original) c cross join
         fillin f left join
         original o
         on o.customer = c.customer and o.rank = f.rank
    where o.rank is null;

You can run the subquery to get the missing values.

The idea is to generate all possible combinations of "fill ins". Then remove the ones where there is already a value.

EDIT:

Oops, I thought the ranking in the fill in table was the final ranking. But you can still do this:

insert into original(customer, product, rank)
    select c.customer, f.product, f.rank
    from (select customer, max(rank) as maxrank from original) c cross join
         fillin f left join
         original o
         on o.customer = c.customer and o.rank - o.maxrank + 1 = f.rank;

Upvotes: 1

Related Questions