Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – What is the difference between these 2 queries?

(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.

QUERY 1:

SELECT
    product_id,
    MIN(year) first_year,
    quantity,
    price
FROM 
    sales
GROUP BY
    product_id

QUERY 2:

select 
    product_id, 
    year as first_year, 
    quantity, 
    price
from 
    Sales
where
    (product_id, year) in (
                            select 
                                product_id, 
                                min(year) 
                            from 
                                Sales
                            group by 
                                product_id
                            )

This is from a problem in LeetCode, my solution is Query#1 (it did not work), however, Query#2 seems to work but I cannot understand what the difference between the two is

Tags:

2

Answers


  1. 0

    Your first query is invalid as it is not ANSI SQL. While it might run on your MySQL tool, such syntax should never be used. Here is a corrected version using proper joins to restrict each product to the record from the earliest year:

    SELECT s1.product_id, s1.year AS first_year, s1.quantity, s1.price
FROM sales s1
INNER JOIN (
    SELECT product_id, MIN(year) AS first_year
    FROM sales
    GROUP BY product_id
) s2
    ON s2.product_id = s1.product_id AND
       s2.first_year = s1.year;
    Login or Signup to reply.
  2. 0

    as mentioned in my comment you must group by everything that is not an aggregate. so if you don’t want to do that but instead want to show all rows where the first year by product id matches then you can use the second query or what I prefer to do is use a windowing function like dense_rank

    so assign a ranking to each year partitioned by the product id and then pick the ones where the ranking is 1.

    WITH RankedSales AS (
    SELECT
        product_id,
        year AS first_year,
        quantity,
        price,
        DENSE_RANK() OVER (PARTITION BY product_id ORDER BY year) AS rnk
    FROM
        Sales
)
SELECT
    product_id,
    first_year,
    quantity,
    price
FROM
    RankedSales
WHERE
    rnk = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search