Complex SELECTs
By Kalman Toth, M.Phil., M.Phil., MCDBA

May 16, 2005

The beauty of SQL language is its uncanny ability to manipulate sets of data. Exactly where other programming languages fall short due to lack of built-in looping. When I write "select * from Alpha" in SQL, I am telling the database engine: loop through the entire Alpha table and return the content of each row.

In the following example instead of joining 2 tables, we are joining 2 SELECT result sets (subselects, derived tables). We added the "Seq" column to the select list and populated it with the value 1. We are using it to generate an ordinal (sequence number) for each CategoryID, SupplierID combination.

-- SQL sequence number (LineNo) generation within groups

USE NorthWind;

SELECT   b.CategoryID,

         b.SupplierID,

         b.ProductName,

         [LineNo] = sum(b.seq)

FROM     (SELECT CategoryID,

                 SupplierID,

                 ProductName,

                 1 AS seq

          FROM   dbo.Products) a

 INNER JOIN

         (SELECT CategoryID,

                 SupplierID,

                 ProductName,

                 1 AS seq

          FROM   dbo.Products) b

ON       a.SupplierID = b.SupplierID

         AND a.CategoryID = b.CategoryID

WHERE    b.ProductName >= a.ProductName

GROUP BY b.CategoryID,

         b.SupplierID,

         b.ProductName

GO

The partial result is below. The RowSeqNo breaks and starts over whenever the CategoryID and/or SupplierID changes.