For the below query I'm using ORDER BY in one of the subqueries to fetch recently added products for some E-Commerce use case. This order is not preserved in the outer query when the DB executes `AND v.product_id IN ( SELECT * ...` 

I executed the same query in MariaDb with same data and in MariaDb the order is preserved. Is there any way in SQLite also to preserve the order of subquery results?

WITH products
AS
  (
         SELECT v.product_id,
                v.variant_id,
                v.title,
                v.price,
                v.position,
                v.image_url,
                i.url
         FROM   shopify_variants v
         JOIN   shopify_images i
         WHERE  v.product_id = i.product_id
         AND    v.product_id IN
                (
                       SELECT *
                       FROM   (
                                       SELECT   p.product_id
                                       FROM     shopify_products p
                                       JOIN     shopify_collections c
                                       WHERE    p.product_id = c.product_id
                                       AND      c.bot_ref = 27674
                                       AND      c.shop_domain = "mystore.com"
                                       AND      p.status LIKE "ACTIVE"
                                       AND      c.title="Casual"
                                       GROUP BY p.product_id
                                       ORDER BY p.created_at DESC
                                       LIMIT    30 ) AS limiter))
  SELECT products.product_id,
         products.position,
         products.variant_id,
         products.title,
         products.price,
         products.url,
         products.image_url,
         pr.handle,
         pr.description,
         pr.title AS producttitle
  FROM   products
  JOIN   shopify_products pr
  WHERE  products.product_id =pr.product_id