Getting MIN Price in Subquery in SQL Server (using DISTINCT)?

Question

Hi there,

I am trying to get a Minimun price from a car in a table i have.. I am using DISTINCT

SELECT DISTINCT

datepart(year,[Registration]) AS YearRegistered, MIN(SalePrice), Model, Make

FROM [VehicleSales]

But its not working, for example

without distinct returns many car makes and models so i use distinct so i get unique cars that are the same make and model and year....

I wish to incorporate a "Startign from price ..." hence the SalePrice can also be different for same model and make ... so i want to do a MIN..

But i am bit confused, the above is working working...

Any ideas?

Answer 1

You need to add a GROUP BY clause and get rid of the DISTINCT:

SELECT 
       datepart(year,[Registration]) AS YearRegistered, 
       MIN(SalePrice), Model, Make
FROM 
       [VehicleSales] 
GROUP BY 
       datepart(year,[Registration]), Model, Make

Answer 2

SELECT  DATEPART(year,[Registration]) AS YearRegistered, Model, Make, MIN(SalePrice)
FROM    [VehicleSales]
GROUP BY
        DATEPART(year,[Registration]) AS YearRegistered, Model, Make