Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to find maximum list price for each product subcategory without and with GROUP BY.

-- SQL category processing without using GROUP BY
USE AdventureWorks;
SELECT   DISTINCT SubcategoryName = d.Name,
                  MaxListPrice = ListPrice
FROM     Production.ProductSubcategory d
         JOIN Production.Product p
           ON d.ProductSubcategoryID = p.ProductSubcategoryID
WHERE    ListPrice > 0.0
AND convert(VARCHAR,p.ProductSubcategoryID) + convert(VARCHAR,ListPrice)
NOT IN (SELECT convert(VARCHAR,p1.ProductSubcategoryID) +
               convert(VARCHAR,p1.ListPrice)
-- SQL self-join
        FROM   Production.Product p1
                INNER JOIN Production.Product p2
           ON p1.ProductSubcategoryID = p2.ProductSubcategoryID
            AND p2.ListPrice > p1.ListPrice)
ORDER BY 1;

GO

 
-- Equivalent GROUP BY statement
SELECT   DISTINCT SubcategoryName = d.Name,
                  MaxListPrice = max(ListPrice)
FROM     Production.ProductSubcategory d
         INNER JOIN Production.Product p
           ON d.ProductSubcategoryID = p.ProductSubcategoryID
WHERE    ListPrice > 0.0
GROUP BY d.Name
ORDER BY SubcategoryName

GO

------------

Related Articles:

Self Join In Sql Server 2000 2005

http://sqlusa.com/bestpractices2005/selfjoin/