CODEWITHSUNDEEP
sql-server
Kinny
Kinny

Reputation: 59

SQL displaying a row with least value without using TOP

im trying to list the row with the least value by not using the top function. enter image description here

Here is my code, 

> SELECT COUNT(SOH.SalesOrderNumber) AS NumberOfSales,  SP.Name FROM Person.StateProvince SP 
JOIN Person.Address A ON SP.StateProvinceID = A.StateProvinceID
JOIN Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID
JOIN Person.BusinessEntityAddress BEA ON A.AddressID = BEA.AddressID
JOIN Sales.SalesOrderHeader AS SOH ON BEA.BusinessEntityID = SOH.CustomerID
WHERE ST.Name = 'Australia'
GROUP BY SP.Name

Ideally, the result should be NumberOfSale Name 94 Tasmania

Thank You!

Upvotes: 0

Views: 40

Answers (1)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

This would be an alternative:

SELECT NumberOfSales
    ,NAME
FROM (
    SELECT COUNT(SOH.SalesOrderNumber) AS NumberOfSales
        , SP.NAME
        , ROW_NUMBER() OVER (
            ORDER BY COUNT(SOH.SalesOrderNumber)) RN
    FROM Person.StateProvince SP
    INNER JOIN Person.Address A
        ON SP.StateProvinceID = A.StateProvinceID
    INNER JOIN Sales.SalesTerritory AS ST
        ON SP.TerritoryID = ST.TerritoryID
    INNER JOIN Person.BusinessEntityAddress BEA
        ON A.AddressID = BEA.AddressID
    INNER JOIN Sales.SalesOrderHeader AS SOH
        ON BEA.BusinessEntityID = SOH.CustomerID
    WHERE ST.NAME = 'Australia'
    GROUP BY SP.NAME
    ) RS
WHERE RN = 1

Upvotes: 2

Related Questions