Select row with max value in one column

Question

I have a select statement that returns two columns: office names and total per office:

select o.OfficeName, c.Total
from Offices o
left join
( select OfficeID, count(*) Total
from Customers c
group by OfficeID
) c on o.OfficeID = c.OfficeID
where o.ClusterID = 29

How can I get the row that has max total? "Customers" table has an "OfficeID" colummn. For a given "ClusterID", I select all offices within the cluster identified by cluster id (e.g. 29) and count the customers belongin to those offices.

GarethD · Accepted Answer · 2012-03-03 07:32:23Z

There are a number of approaches:

SELECT  OfficeName, Total
FROM    (   SELECT  o.OfficeName, c.Total, MAX(Total) OVER() [MaxTotal]
            FROM    Offices o
                    LEFT JOIN
                    (   SELECT  OfficeID, COUNT(*) Total
                        FROM    Customers
                        GROUP BY OfficeID
                    ) c 
                        ON o.OfficeID = c.OfficeID
            WHERE   o.ClusterID = 29
        ) c
WHERE   Total = MaxTotal

OR

WITH CTE AS
(   SELECT  o.OfficeName, c.Total
    FROM    Offices o
            LEFT JOIN
            (   SELECT  OfficeID, COUNT(*) Total
                FROM    Customers
                GROUP BY OfficeID
            ) c 
                ON o.OfficeID = c.OfficeID
    WHERE   o.ClusterID = 29
)
SELECT  *
FROM    CTE
WHERE   Total = (SELECT MAX(Total) FROM CTE)

OR

SELECT  TOP 1 o.OfficeName, c.Total
FROM    Offices o
        LEFT JOIN
        (   SELECT  OfficeID, COUNT(*) Total
            FROM    Customers
            GROUP BY OfficeID
        ) c 
            ON o.OfficeID = c.OfficeID
WHERE   o.ClusterID = 29
ORDER BY Total DESC

Although using TOP 1 may not be what you are after, with the other methods if there are 2 offices with the same number of customers they will both be returned, whereas TOP 1 will only return 1 of these (probably in order of office name). If you only ever want 1 record, then this is the best method

Steven Schroeder · Accepted Answer · 2012-03-03 07:24:02Z

1

SELECT TOP 1 o.OfficeName, c.Total
  FROM Offices o
  LEFT JOIN
    (SELECT OfficeID, count(*) Total
       FROM Customers c
      GROUP BY OfficeID
    ) c ON o.OfficeID = c.OfficeID
 WHERE o.ClusterID = 29
 ORDER BY c.Total DESC

answered Mar 3, 2012 at 7:24

Steven Schroeder

6,2742 gold badges23 silver badges15 bronze badges

2 Comments

NoBullMan Over a year ago

Steven and @GarethD, Thank you. I wasn't thinking about the situation where two or more offices might have the same number of customers. This can happen in my case and I wold want to display all.

NoBullMan Over a year ago

Why can't I mark both responses as answer; they were both correct?

John Dewey · Accepted Answer · 2012-03-03 14:28:49Z

1

WITH TIES offers a cleaner way to get all offices sharing the top count:

with a as (
    select o.OfficeID,Total=COUNT(*)
    from Offices o
    inner join Customers c on c.OfficeID=o.OfficeID
    group by o.OfficeID
)
select top 1 WITH TIES t.OfficeName, a.Total
from a
inner join Offices t on t.OfficeID=a.OfficeID
where t.ClusterID=29
order by a.Total desc

answered Mar 3, 2012 at 14:28

John Dewey

7,1234 gold badges25 silver badges27 bronze badges

Collectives™ on Stack Overflow

Select row with max value in one column

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related