0

I have tables like these:

INSERT INTO listings 
(id, external_id, variation_id, product_id) VALUES
(101, '9900001', '9900001var1', 1),
(102, '9900001', '9900001var2', 4),
(103, '9900002', '9900002var1', 1),
(104, '9900002', '9900002var2', 2),
(105, '9900003', '9900003var1', 3),
(106, '9900003', '9900003var2', 4);

INSERT INTO products
(id, price) VALUES
(1, 101),
(2, 100),
(3, 100),
(4, 102);

Which means that there are 3 listings (9900001, 9900002, 9900003) with 2 products each (1, 4), (1, 2) and (3, 4) respectively.

What I need is to retrieve one single row for each listing, with the id (not the price) of the product with the highest price in that listing.

So, the desired output would be:

id    | external_id | variation_id | product_id
[ANY]   9900001       [ANY]          4
[ANY]   9900002       [ANY]          1
[ANY]   9900003       [ANY]          4

The closest I got to the desired answer was with this query:

SELECT
    p.id AS product_id_max,
    p.price AS product_price_max,
    MAX(p.price) AS product_price_max_max,
    listings.* 
            FROM listings
            INNER JOIN (

              -- Subquery tested above:
              SELECT DISTINCT pp3.* FROM 
                (SELECT MAX(p2.price) as max_price
                FROM products p2
                INNER JOIN listings l2 ON l2.product_id = p2.id 
                GROUP BY l2.external_id) pp2
              INNER JOIN
                (SELECT p3.* FROM products p3 ) pp3
              ON
                pp2.max_price = pp3.price
              ORDER BY pp3.price DESC

            ) AS p
            ON p.id = listings.product_id
            -- WHERE MAX(p.price) = p.price
            GROUP BY external_id
            -- HAVING MAX(p.price) = p.price
            ORDER BY product_price_max DESC

Uncommenting the WHERE clause throws an error, uncommenting the HAVING clause returns less rows than desired. Leavin both commented give the correct rows but wrong values in the product_id column.

Fiddle: http://sqlfiddle.com/#!9/d58d665/54/0

Sebastián Grignoli
  • 32,444
  • 17
  • 71
  • 86
  • @bill-karwin that link you posted answers a different question. Sound similar but the problem is different. – Sebastián Grignoli Jul 15 '19 at 22:11
  • Okay, I reversed the duplicate-question link. I will leave the [tag:greatest-n-per-group] tag, because it belongs to that general type of question. – Bill Karwin Jul 15 '19 at 22:57
  • 1
    Since MySQL 8.0 added window functions, every greatest-n-per-group question should include the version of MySQL. If you can use MySQL 8.0+, you should use window functions. – Bill Karwin Jul 15 '19 at 22:58
  • Wow! Window functions was the answer I was looking for! I use MariaDB 10.1.22 but I guess I can upgrade to 10.2 which is were they added support for those. Thanks! – Sebastián Grignoli Jul 15 '19 at 23:37
  • I'll soon edit this question to reflect what's really achieved here. For now I changed the title. – Sebastián Grignoli Jul 16 '19 at 17:10
  • So are you using MySQL or MariaDB? They are two different products. MariaDB forked in 2010, and it has slowly been diverging since then. I don't consider them to be compatible products anymore. You should be clear in your question which one you're using. – Bill Karwin Jul 16 '19 at 17:24
  • I'm using MariaDB without window functions but I will upgrade to a version that supports them. The fiddle I used to work on the solution uses MySQL 5.6. I will use window functions in production but given that I already got this working without them I think we can let this question exist to answer how to do that for those stuck with old engines for any reason. – Sebastián Grignoli Jul 16 '19 at 18:10
  • I will rewrite both the question and the answer to clarify the situation with SQL engines and versions in wich this answer applies. – Sebastián Grignoli Jul 16 '19 at 18:12

1 Answers1

0

I figured it out:

SELECT
    listings.*,
    max_p.product_id AS max_product_id
            FROM listings
            INNER JOIN (

              SELECT DISTINCT max_external_id as external_id, pp3.id as product_id FROM
                (SELECT MAX(p2.price) as max_price, l2.external_id as max_external_id
                FROM products p2
                INNER JOIN listings l2 ON l2.product_id = p2.id
                GROUP BY l2.external_id) pp2
              INNER JOIN
                (SELECT p3.* FROM products p3 ) pp3
              ON
                pp2.max_price = pp3.price
              ORDER BY pp3.price DESC

            ) AS max_p
            ON max_p.external_id = listings.external_id
            GROUP BY listings.external_id

As tested here:

http://sqlfiddle.com/#!9/d58d665/53/0

Sebastián Grignoli
  • 32,444
  • 17
  • 71
  • 86