ORDER BY results of a query (nested?)

Question

Note: I have browsed similar questions, but they didn't answer my question.

To practice SQL, I have created a testing database in MS Access. It stores information about a car shop's business. The layout is like so:

enter image description here

I wish to list (group by them) the car makes with the service that is most frequently bought/ordered for the given make. I don't know how to use the ORDER BY clause here. SO: each make should only be shown once, with its most frequently purchased service.

Example:
Porsche | Oil change
Ford | Tire change
Chevrolet | Brake fluid change
etc.

I suppose I should somehow nest it, but I'm at a loss as to how to exactly do it.

I was able to build the following query, which will list all car-service relations:

SELECT cars.Make, services.[Service name]
FROM (repairs 
INNER JOIN cars ON cars.[Car number] = repairs.[Car number]) 
INNER JOIN services ON services.ID = repairs.[Service ID]
GROUP BY cars.Make, services.[Service name];

enter image description here

How would I build this query properly, as described above?

Just add the desired ORDER BY clause *after* the GROUP BY clause. Note that (in general - there are exceptions) the ordering of the fields in the GROUP BY is **not** significant, but in the ORDER BY clause **is** significant. — Pieter Geerkens, Mar 05 '15 at 21:15
Well, as stated in the question, I don't know what I want to `ORDER BY` - there isn't a column that contains these values. — user4520, Mar 05 '15 at 21:16

bbrumm · Accepted Answer · 2015-03-16T23:42:08.847

1

Could you try this query, which uses a MAX and COUNT inside subqueries?

SELECT sub1.make, sub1.[Service name]
FROM (
    SELECT cars.Make, services.[Service name], COUNT(*) as COUNTREPAIR
    FROM (repairs 
    INNER JOIN cars ON cars.[Car number] = repairs.[Car number]) 
    INNER JOIN services ON services.ID = repairs.[Service ID]
    GROUP BY cars.Make, services.[Service name]
) sub1
INNER JOIN
    (SELECT sub2.make, MAX(COUNTREPAIR) as MAXCOUNT
    FROM 
        (
        SELECT cars.Make, services.[Service name], COUNT(*) as COUNTREPAIR
        FROM (repairs 
        INNER JOIN cars ON cars.[Car number] = repairs.[Car number]) 
        INNER JOIN services ON services.ID = repairs.[Service ID]
        GROUP BY cars.Make, services.[Service name]
        ) sub2
    GROUP BY sub2.Make
    ) sub3
ON sub1.COUNTREPAIR = sub3.MAXCOUNT
AND sub1.MAKE = sub3.MAKE;

I put together an Access database and some dummy data that mimics yours, so hopefully it gives you the result you need.

If you wanted the COUNT of these services, you can add a "sub1.COUNTREPAIR" at the end of the first line.

edited Mar 16 '15 at 23:42

answered Mar 06 '15 at 01:26

bbrumm

1,342
1
8
13

Well, it complains the Make column in the outer `SELECT` is not part of an aggregate function... which makes sense, it is not. – user4520 Mar 06 '15 at 17:27
Of course - forgot to add that one in. I updated the query - just needed to add a GROUP BY at the end. Does this work now? – bbrumm Mar 07 '15 at 06:08
Nope, Porsche is still listed twice: http://i.gyazo.com/0b2e24e5ea998f9092aa14307d7724e9.png – user4520 Mar 07 '15 at 11:44
Hmm I see what you mean. I re-read your question and see what you're after. How about the query above now? I removed the Service Name from the outer query, and it should just show Make with the COUNT of records for each Make. – bbrumm Mar 10 '15 at 18:17
Well, yes, it does work as you've described, but that's not what I'm after. I want to list the service, not record count (refer to question for examples) – user4520 Mar 13 '15 at 18:57
I've updated the query after putting together an Access database based on what you had in your question. Does the query work now? – bbrumm Mar 16 '15 at 23:44
Holy s... cow, I appreciate your effort, it works beautifully :) – user4520 Mar 17 '15 at 19:33

ORDER BY results of a query (nested?)

1 Answers1