CODEWITHSUNDEEP
sql-server-2008t-sql
Alaa Alweish
Alaa Alweish

Reputation: 9084

Using aggregate function in a CTE query

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.

Upvotes: 3

Views: 8601

Answers (2)

marc_s
marc_s

Reputation: 754388

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!

Upvotes: 7

Lori Lalonde - MSFT
Lori Lalonde - MSFT

Reputation: 368

You will need to decide why you are obtaining MAX(Row). Is it the max row by Ad_Date? Is the max row overall?

If you change it to:

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

...that will return you the max row by Ad_Date which is what I'm assuming you are looking for.

Upvotes: 1

Related Questions