Displaying Other values of the row after getting the max of a subquery

Question

So I have is this query:

select MAX(C.Sales) as 'NumOfSales'
from
    (select COUNT(SalesOrderID) as 'Sales',
    TerritoryID
    from Sales.SalesOrderHeader
    group by TerritoryID) C

right now it just shows the max of C.Sales I want to be able to get the Territory of the max value.

Have a look at this already existing solution: https://stackoverflow.com/a/7745635/2610061 — Carsten Massmann, Oct 23 '19 at 19:21
Which DBMS are you using? You tagged both mysql and sql server and somebody removed one of them without having clarification. The solution is different for each DBMS — Sean Lange, Oct 23 '19 at 19:25
I am using Microsoft SQL server Management. Also I figured it out using the solution that cars10m commented. — Seth Claur, Oct 23 '19 at 19:33

Carsten Massmann · Answer 1 · 2019-10-23T19:44:41.917

0

If you want one record only, then the following should do the job:

MySql:

select COUNT(SalesOrderID) as Sales,
TerritoryID
from Sales.SalesOrderHeader
group by TerritoryID
order by Sales desc limit 1

Sql-Server:

select top 1 COUNT(SalesOrderID) as Sales,
TerritoryID
from Sales.SalesOrderHeader
group by TerritoryID
order by Sales desc

edited Oct 23 '19 at 19:44

answered Oct 23 '19 at 19:24

Carsten Massmann

26,510
2
22
43

score 0 · Answer 2 · answered Oct 23 '19 at 19:30

Order by descending on taking the top 1 row:

SELECT TOP 1 COUNT(SalesOrderID) AS Sales,
    TerritoryID
FROM Sales.SalesOrderHeader
GROUP BY TerritoryID
ORDER BY Sales DESC

If you need all rows that have the same max amount of sales, it is more involved: Here is a way to achieve this with some example data:

DECLARE @SalesOrerHeader TABLE 
( 
    SalesOrderID INT IDENTITY(1,1),
    TerritoryID INT
)

INSERT INTO @SalesOrerHeader (TerritoryID) VALUES
(1),(1),(2),(2),(2),(2),(3),(4),(4),(4),(5),(5),(5),(5)

DECLARE @final TABLE 
(
    TerritoryID INT,
    NumberOfSales INT
)

INSERT INTO @final
SELECT TerritoryID, COUNT(SalesOrderID) AS NumberOfSales FROM @SalesOrerHeader
GROUP BY TerritoryID

SELECT * FROM @final
WHERE NumberOfSales = (SELECT MAX(NumberOfSales) FROM @final)

OUTPUT:

TerritoryID NumberOfSales
2           4
5           4

Or a simpler approach to get both rows with the same value would be to do "top 1 with ties". Then you would get both. https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15 — Sean Lange, Oct 23 '19 at 19:32

score 0 · Answer 3 · answered Oct 23 '19 at 19:31

It should be like this if you want both C.Sales and C.TerritoryID:

select MAX(C.Sales) as 'NumOfSales', C.TerritoryID
from
    (select COUNT(SalesOrderID) as 'Sales',
    TerritoryID
    from Sales.SalesOrderHeader
    group by TerritoryID) C

If you only want TerritotyID you can add this:

Select T.TerritoryID
from
    (select MAX(C.Sales) as 'NumOfSales', C.TerritoryID
    from
        (select COUNT(SalesOrderID) as 'Sales',
        TerritoryID
        from Sales.SalesOrderHeader
        group by TerritoryID) C
    ) T
ORDER BY T.NumOfSales

score 0 · Answer 4 · answered Oct 23 '19 at 19:35

select K.Sales, K.TerritoryID
from(select COUNT(SalesOrderID) as 'Sales',
    TerritoryID
    from Sales.SalesOrderHeader
    group by TerritoryID) K
    inner join (
        select MAX(M.Sales) as 'MostSales'
        from(
            select COUNT(SalesOrderID) as 'Sales'
            from Sales.SalesOrderHeader
            group by TerritoryID) M
        ) MaxSales on K.Sales = MaxSales.MostSales

This the solution that I came up with after a little more research

Displaying Other values of the row after getting the max of a subquery

4 Answers4