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.

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.

here is the db-fiddle with the row_number added.

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

any help appreciated. thankyou

>Solution :

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