Geek asks and answers

Using aggregate function in a CTE request

The following query works 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 adding, MAX(Row)SQL Server 2008 throws the following error:

The column "QResult.Row" is not valid in the selection list because it is not contained either in the aggregate function or in the expression GROUP BY.

+3
source share
2 answers

When using an aggregate function, such as SUM, COUNTor MAX, and you also want to select other columns from your data, you need to group your data by other columns 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

, - . * GROUP BY.

: , , - :
(. № 2 - , )

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

Row CTE, .

№2: :

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

, , - , . , !

+6

, MAX (Row). Ad_Date? ?

:

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

..., Ad_Date, , .

+1

More articles:


All Articles