O'Reilly logo
  • Dustin Waybright thinks this is interesting:

Another workaround is to apply an aggregate function like MAX to the column, as follows.

SELECT S.shipperid,
  MAX(S.companyname) AS companyname,
  COUNT(*) AS numorders
FROM Sales.Shippers AS S
  INNER JOIN Sales.Orders AS O
    ON S.shipperid = O.shipperid
GROUP BY S.shipperid;


Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012


Versus adding the company name to the GROUP BY clause, MAX selects the most frequently occurring company name string for the group. Since we know only 1 company name should exist per shipper ID, it is valid in this example.