Using aggregate function in a CTE query

Question

The following query is working fine without ',MAX(Row)'

WITH QResult AS 
(SELECT 
    ROW_NUMBER() OVER (ORDER BY Ad_Date DESC) AS Row,
    * 
 FROM [vw_ads]
) 
SELECT *, MAX(Row) 
FROM QResult

When MAX(Row) is added, SQL Server 2008 is throwing the following error :

Column 'QResult.Row' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

When using an aggregate function like SUM, COUNT or MAX, and you want to also select other columns from your data, then you need to group your data by the other column(s) used in your query.

So you need to write something like:

WITH QResult AS 
(SELECT 
    ROW_NUMBER() OVER (ORDER BY Ad_Date DESC) AS Row,
    * 
 FROM [vw_ads]
) 
SELECT Co1l, Col2, MAX(Row) 
FROM QResult
GROUP BY Col1, Col2

This also means you need to explicitly spell out the columns you want - a good idea in any case. You cannot use * in a GROUP BY clause.

Update: based on your comment, I guess what you really want is something like this:
(see Update #2 - Martin Smith's suggestion is even better than my original idea here)

WITH QResult AS 
(SELECT 
    ROW_NUMBER() OVER (ORDER BY Ad_Date DESC) AS Row,
    * 
 FROM [vw_ads]
) 
SELECT 
    Co1l, Col2, 
    MaxRow = (SELECT MAX(Row) FROM QResult)
FROM QResult

This will give you the maximum value of Row from the CTE, the same value, for each row of your result set.

Update #2: Martin Smith's suggestion would be this:

WITH QResult AS 
(SELECT 
    ROW_NUMBER() OVER (ORDER BY Ad_Date DESC) AS Row,
    * 
 FROM [vw_ads]
) 
SELECT 
    Co1l, Col2, 
    MAX(Row) OVER()
FROM QResult

and of course, this works, too - and even more efficient than my solution. Thanks, Martin!