CODEWITHSUNDEEP
mysqlsqlselect
BobbyBob
BobbyBob

Reputation: 3

Select from a Selected SQL query

I have a SQL table named "ORDERS" with two attributes, {orderNumber(primary key), and customerNumber}. When I use 

SELECT Orders.CustomerID, COUNT(Orders.OrderID) AS numOfOrders
FROM Orders
GROUP BY Orders.CustomerID
ORDER BY numOfOrders DESC
LIMIT 1;

I get the results "11 18". which correspond to CustomerID: 11 made 18 orders( in this case, the customer that made the most order).

How do I return just the CustomerID and not the numOfOrders?

Upvotes: 0

Views: 3169

Answers (2)

anjali
anjali

Reputation: 84

Make the main query as subquery and then select only customer id from the table. 

SELECT CustomerID from(SELECT Orders.CustomerID, COUNT(Orders.OrderID) AS numOfOrders
FROM Orders
GROUP BY Orders.CustomerID
ORDER BY numOfOrders DESC) a
LIMIT 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Just use COUNT() in the ORDER BY. I would do:

SELECT o.CustomerID
FROM Orders o
GROUP BY o.CustomerID
ORDER BY COUNT(*) DESC
LIMIT 1;

Upvotes: 2

Related Questions