How to split table up into multiple tables with unique rows based on sku - Mysql

user16881755
March 7, 2023
145 views
0 votes
2 Answers

I have a table with tier pricing showing the more products purchased the better the price.

I have to import this table into another program that only lets me import products with the same sku once. This means I have to split the csv and export it into multiple csv’s.

Below is example of my table however my actual table has a lot more results/rows.

sku	qty	price
abc	15	10.00
abc	10	8.00
abc	5	7.00
xyz	10	5.00
bbb	8	3.50
xxx	14	27.00
xxx	12	20.50
xxx	10	15.50
xxx	8	10.00

I would say the maximum amount of tiers is 4 like sku ‘xxx’ so this would mean I would need a total of 4 statements.

I am struggling how to work out and split up the data into 4 separate tables.

I did try adding a row_number function and then thought maybe I could just export the odd/even row_numbers but that wouldn’t work when working with qty’s more than 2.

Is there another MySQL function I have not found yet that can perform this task?

The end result I am trying to achieve is like this with 4 statements for each tier.

sku	qty	price
abc	15	10.00
xyz	10	5.00
bbb	8	3.50
xxx	14	27.00

sku	qty	price
abc	10	8.00
xxx	12	20.50

sku	qty	price
abc	5	7.00
xxx	10	15.50

sku	qty	price
xxx	8	10.00

here is the db-fiddle with the row_number added.

https://www.db-fiddle.com/f/ofmx1KCHBjpRhHLonjkReq/0

any help appreciated. thankyou

Tags: mysql sql

Answers

- Isolated
- March 7, 2023 at 9:49 pm
- 0 votes
0
Using this row_number() function works in MySQL 8.0 and I believe works in your version of MariaDB. Try running just the CTE part of the query first to ensure it’s compatible and to better understand how that function works.
```
with tiers as (
  select sku, qty, price, 
    row_number() over (partition by sku order by qty desc) as rn
  from orders_details
  )
select sku, qty, price
from tiers
where rn = 1 --repeat for 2, 3 and 4, each separately
```
Login or Signup to reply.

- nbk
- March 7, 2023 at 10:05 pm
- 0 votes
0
Even in old Mysq or Maroadb version can this be achieved

Query #1
```
SELECT 
       IF(sku = @sku, @rownum := @rownum + 1, @rownum := 1) AS `rank'`,
          @sku := sku as sku,
       t.qty,t.price
  FROM (SELECT * FROM orders_details ORDER BY sku,qty DESC LIMIT 18446744073709551615) t, 
       (SELECT @rownum := 0, @sku := '') r
ORDER BY 1;
```
rank’ sku qty price

1 abc 15 10

1 xyz 10 5

1 bbb 8 4

1 xxx 14 27

2 abc 10 8

2 xxx 12 21

3 xxx 10 16

3 abc 5 7

4 xxx 8 10

View on DB Fiddle
Login or Signup to reply.