CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate-functions
mark smith
mark smith

Reputation: 20907

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

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?

Upvotes: 0

Views: 2112

Answers (2)

Quassnoi
Quassnoi

Reputation: 425663

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

Upvotes: 1

Philippe Leybaert
Philippe Leybaert

Reputation: 171864

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

Upvotes: 3

Related Questions