Sql Top 1 Syntax For A Nested Query

New to SQL Server and I am trying to use top 1 to get the company with the most order in my DB within my code that is already working but I don't know how to use it properly. Only

Solution 1:

You need to give the derived table an alias, and also, specifying top without an order by clause is pretty pointless as rows are returned as a set without any order unless the order is explicitly specified with an 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
  GROUPby c.CompanyName
) AS YourTable
ORDERBY something_meaningful_maybe_nombre_commande?

Solution 2:

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
GROUPby c.CompanyName
ORDERBY Nombre_Commande DESC;

This assumes that Nombre_Commande is what you want to order by.

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