DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to perform the equivalent of GROUP BY?

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

SubcategoryName MaxListPrice
Bib-Shorts 89.99
Bike Racks 120
Bike Stands 159
Bottles and Cages 9.99
Bottom Brackets 121.49
Brakes 106.5
Caps 8.99
Chains 20.24
Cleaners 7.95
Cranksets 404.99
Derailleurs 121.46
Fenders 21.98
Forks 229.49
Gloves 37.99
Handlebars 120.27
Headsets 124.73
Helmets 34.99
Hydration Packs 54.99
Jerseys 53.99
Lights 44.99
Locks 25
Mountain Bikes 3399.99
Mountain Frames 1364.5
Panniers 125
Pedals 80.99
Pumps 24.99
Road Bikes 3578.27
Road Frames 1431.5
Saddles 52.64
Shorts 69.99
Socks 9.5
Tights 74.99
Tires and Tubes 35
Touring Bikes 2384.07
Touring Frames 1003.91
Vests 63.5
Wheels 357.06

 
-- 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/


 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE