Geek Answers Handbook

SQL TOP 1 Syntax for Subquery

New to SQL Server, and I'm trying to use the top of 1 to get the company with the most orders in my database in my code that already works, but I don’t know how to use it correctly. It seems to me that only the missing syntax is missing.

Request # 1 works fine:

SELECT c.CompanyName, COUNT(DISTINCT OrderID) as Nombre_Commande FROM Orders O INNER JOIN Customers C ON O.CustomerID = c.CustomerID GROUP BY c.CompanyName

What am i trying to do

 SELECT TOP (1) * FROM (SELECT c.CompanyName, COUNT(DISTINCT OrderID) AS Nombre_Commande FROM Orders O INNER JOIN Customers C ON O.CustomerID = c.CustomerID GROUP BY c.CompanyName)
0
sql
source share
2 answers

You need to give the view a nickname, and also specifying the top element without the order clause is pretty pointless, since the rows are returned as a set without any order, unless the order is explicitly specified with the order by clause:

 SELECT TOP (1) * FROM ( SELECT c.CompanyName, COUNT(DISTINCT OrderID) as Nombre_Commande FROM Orders O INNER JOIN Customers C ON O.CustomerID=c.CustomerID GROUP by c.CompanyName ) AS YourTable ORDER BY something_meaningful_maybe_nombre_commande?
+4
source share

How about this?

 SELECT TOP 1 c.CompanyName, COUNT(DISTINCT OrderID) as Nombre_Commande FROM Orders O INNER JOIN Customers C ON O.CustomerID = c.CustomerID GROUP by c.CompanyName ORDER BY Nombre_Commande DESC;

This suggests that Nombre_Commande is what you want to order.

By the way, I would be surprised if COUNT(DISTINCT) really needed for this request. COUNT(*) or COUNT(OrderId) should be enough.

+2
source share

More articles:


All Articles