Combining two queries 1

[mal2ty]

I'm trying to get the names of customers who have placed the 5 highest orders. I'm able to run one query to find the highest order, and another query to find the number of orders placed by each customer. Is there a way to combine these two queries to obtain the desired results?

Here are the queries I've developed:
SELECT CUSTOMERS.CNAME, COUNT(DISTINCT ORDERS.ONO)
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.CNO = ORDERS.CNO
GROUP BY CUSTOMERS.CNAME;

SELECT MAX(COUNT(DISTINCT ORDERS.ONO))
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.CNO = ORDERS.CNO
GROUP BY CUSTOMERS.CNAME;

Here are the relations I'm working with:
CUSTOMERS(CNO,CNAME,STREET,ZIP,PHONE)
ORDERS(ONO,ENO,RECEIVED,SHIPPED)

 

[mal2ty]

I found one answer to my own question. Is this the best way to do it?

SELECT *
FROM (SELECT CUSTOMERS.CNAME, COUNT(DISTINCT ORDERS.ONO)
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.CNO = ORDERS.CNO
GROUP BY CUSTOMERS.CNAME
ORDER BY COUNT(DISTINCT ORDERS.ONO) DESC)
WHERE ROWNUM < 6;

 

[carp]

I think this is one of those questions whose answer is &quot;it depends on the RDBMS you're using&quot;.

As a starter, you can use

SELECT CUSTOMERS.CNAME, COUNT(DISTINCT ORDERS.ONO) num_orders
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.CNO = ORDERS.CNO
GROUP BY CUSTOMERS.CNAME
ORDER BY num_orders DESC;

This will give the list in descending order, but will have ALL customers in it.

If you are using Oracle, you can try

SELECT * FROM
(SELECT CUSTOMERS.CNAME, COUNT(DISTINCT ORDERS.ONO) num_orders
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.CNO = ORDERS.CNO
GROUP BY CUSTOMERS.CNAME
ORDER BY num_orders DESC)
WHERE rownum < 6;

Other RDBMS will have similar mechanisms.