Without your db schema it is kind of hard to answer this; as we can only assume what data you have.
You have referenced the table
CarRental in your query. I am guessing that these are distinct reservations for a particular car, identified by the column
CarID. If this were my (normalized) DB, I would also have a table for
Car with a PK of CarID, as referenced by the rental table.
If all of this is true; the query you have presented would provide you details about the reservations and not so much about the cars themselves. Even if that is what you wanted you are not going to get the 5 most popular as your aggregation is only on the CarID (essentially doing nothing) and sorting by CarID. It is a reasonable assumption then that the subquery is going to always respond with the last 5 CarIDs in descending order
What you would want to do is to keep the aggregate based on CarID, however; you want to do the order on the Count of those CarIDs, descending
SELECT *
FROM CarRental
WHERE CarID IN (
SELECT TOP (5) CarID
FROM CarRental
GROUP BY CarID
ORDER BY Count(CarID) DESC
)
If I was right on my db schema, that you have a Car table; and you wanted the Car details instead- the query is an easy edit to have the main query reference that table and keeping the subquery as is:
SELECT *
FROM Car
WHERE CarID IN (
SELECT TOP (5) CarID
FROM CarRental
GROUP BY CarID
ORDER BY Count(CarID) DESC
)