(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
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:
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.