Cars Sold in First Year

    

Write an SQL query to fetch Car_ID, Year, Color, Price from the Cars table for the first year of every car sold.

Input :-

Cars table :-
There is no Primary Key column for the Cars table.

Car_ID	Year	Color	Price
105	2009	Red	100000
105	2006	White	500000
112	2012	Black	700000
112	2010	Blue	900000

Output :-

Car_ID	Year	Color	Price
105	2006	White	500000
112	2010	Blue	900000

Query :-

select distinct Car_ID, Year, Color, Price
from Cars
where (Car_ID, Year) in 
(select Car_ID, min(Year)
from Cars
group by Car_ID);

Explanation : - 

The SQL query retrieves distinct car records from the "Cars" table, including the car ID, year, color, and price. The query employs a subquery to filter the results based on specific conditions. Here is the breakdown of the query:

• The outer query begins with the ‘Select distinct’ statement, which ensures that only unique combinations of car ID is returned. The distinct keyword eliminates any duplicate rows that may exist.
• The outer query specifies the columns to be selected :- Car_ID, Year, Color, and Price.
• The subquery (select Car_ID, min(Year) from Cars group by Car_ID) is executed first. It retrieves the minimum year for each Car_ID in the "Cars" table using the ‘Min’ function and groups the results by Car_ID using the ‘Group by’ clause.
• The outer query then checks if each car's (Car_ID, Year) combination exists in the result set of the subquery. This comparison is done using the (Car_ID, Year) in (subquery) syntax.

In summary, this query retrieves the earliest year record for each car in the "Cars" table. It ensures that only distinct combinations of car ID and other corresponding parameters are retrieved, filtering out any duplicates.