skip to Main Content

(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

2

Answers


  1. 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. 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