A little help please

[silviadana]

I have problems with subquerys, in fact I don't know how to avoid using the same condition in the query and subquery.

For example, i have the table

cars(Carnumber,DateofAcquisition)

I need to find out in which of the last five years were purchased the most cars??

SELECT YEAR(DateofAcquisition), COUNT(CarNumber)
FROM cars
WHERE YEAR(dateofAcquisition)>=YEAR(Date())-5
GROUP BY YEAR(DateofAcquisition)
HAVING COUNT(CarNumber)>=ALL (SELECT COUNT(carNumber)
FROM cars
WHERE YEAR(DateofAcquisition)>=YEAR(date())-5)
GROUP BY YEAR(DateofAcquisition))

I want to avoid using the same condition

WHERE YEAR(dateofAcquisition)>=YEAR(Date())-5

 

[pbaldy]

If you're looking for the year with the most acquisitions, you don't need a subquery:

SELECT TOP 1 YEAR(DateofAcquisition), Count(*) AS CountOfyear
FROM cars
GROUP BY YEAR(DateofAcquisition)
ORDER BY Count(*) DESC

 

[silviadana]

thanks a lot.