GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful?
I have used this technique (grouping on a column not used in the SELECT list) to combine an ordered list of values (which has gaps) into ranges of contiguous values. 

First I do a rownum() on the ordered set of values. Then we diff the rownum() and the value. By grouping on the diff, we get the ranges.

CREATE TABLE #TMP (ID INT)

INSERT INTO #TMP 
SELECT 1 UNION 
SELECT 2 UNION 
SELECT 3 UNION 
SELECT 4 UNION 
SELECT 6 UNION 
SELECT 8 UNION 
SELECT 9 UNION 
SELECT 10 UNION 
SELECT 15 UNION 
SELECT 16 UNION 
SELECT 17 UNION 
SELECT 18 UNION 
SELECT 19 UNION 
SELECT 20



WITH RN AS 
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY ID) AS RN, ID 
    FROM 
        #TMP
),
SRC AS 
(
    SELECT 
        RN, 
        ID, 
        ID-RN DIFF 
    FROM 
        RN
)
SELECT 
    MIN(ID) RANGE_START, 
    MAX(ID) RANGE_END, 
    COUNT(*) CNT_VALUES_IN_RANGE 
FROM 
    SRC 
GROUP BY 
    DIFF