Write an SQL query to find the customer_number
for the customer who has placed the largest number of orders.
table name: Orders
Question Explanation:
- We have given a table named orders in which order_number is primary key. But customer_number can have repeated values based on how many times customer has ordered something..
- Each time when a particular customer will order something a new record is going to be added with particular cutomer_number and unique order_number.
- Our task is to find a customer who has placed order maximum times.
Solution Explanation:
- Since, customer_number is repeated multiple times. We are going to apply group by clause on customer_number. It wil create different groups for each unique customer_number.
- Then we will count number of rows in each group.
select COUNT(order_number),customer_number from Orders
group by customer_number
- Order by count(rows) descending.
select COUNT(order_number),customer_number from Orders
group by customer_number
ORDER BY COUNT(order_number) desc
- Select top 1 customer_number.
Final Solution:
select top 1 customer_number from Orders
group by customer_number
ORDER BY COUNT(order_number) desc
Output:
SQL Script:
CREATE TABLE [dbo].[Orders](
[order_number]
[int] NULL,
[customer_number]
[int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Orders] ([order_number], [customer_number]) VALUES (1, 1)
GO
INSERT [dbo].[Orders] ([order_number], [customer_number]) VALUES (2, 2)
GO
INSERT [dbo].[Orders] ([order_number], [customer_number]) VALUES (3, 3)
GO
INSERT [dbo].[Orders] ([order_number], [customer_number]) VALUES (4, 3)
GO