enrique_aeo
asked on
sql server: As I can do it without using CTE expression
hi experts, i have this query
USE AdventureWorks;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
sql server: As I can do it without using CTE expression?
USE AdventureWorks;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
sql server: As I can do it without using CTE expression?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is no difference in actual execution plan (just run it in SQL2005).
There is no real need for the CTE in that case. The "computed" column is a date function and can be just as easily be repeated in the "group by". The Order By can use the alternate method of specifying column numbers - referring to the ordinal position of the items in the selected columns.
Does that make sense ?
There is no real need for the CTE in that case. The "computed" column is a date function and can be just as easily be repeated in the "group by". The Order By can use the alternate method of specifying column numbers - referring to the ordinal position of the items in the selected columns.
Does that make sense ?
Hi,
HA ha ha ha.....
very Nice one mark sir....very nice one....
HA ha ha ha.....
very Nice one mark sir....very nice one....
:) thanks.
But yours is right when there is a need to be able to express highly complex columns and/or dependant columns (on the computed ones), or subsequently refer to columns (in the order by) which might not be part of the selected columns
Then the CTE becomes (as per your posting) a select on the subquery. e.g.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales -- remove salesyear from selected columns
FROM
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)A
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear -- here we cannot use the alternate method of column numbers - have to use column names because it is in the subquery.
So, horses for courses :)
But yours is right when there is a need to be able to express highly complex columns and/or dependant columns (on the computed ones), or subsequently refer to columns (in the order by) which might not be part of the selected columns
Then the CTE becomes (as per your posting) a select on the subquery. e.g.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales -- remove salesyear from selected columns
FROM
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)A
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear -- here we cannot use the alternate method of column numbers - have to use column names because it is in the subquery.
So, horses for courses :)
ASKER
hi can yo give me an example about
1. You coudl reference it multiple times, in different subqueries, and only have it run once
2. The other advantage is that you can do things in a CTE that require a roll-up and can't normally be done in-line, like use a ROW_NUMBER OVER statement.
1. You coudl reference it multiple times, in different subqueries, and only have it run once
2. The other advantage is that you can do things in a CTE that require a roll-up and can't normally be done in-line, like use a ROW_NUMBER OVER statement.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You wanted this way ?
Open in new window