Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

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?
0
enrique_aeo
Asked:
enrique_aeo
3 Solutions
 
Bhavesh ShahLead AnalysistCommented:
Hi,

You wanted this way ?


SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
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;

Open in new window

0
 
Ryan McCauleyCommented:
There are two ways to do this without the CTE, but they're both longer and more complicated.

You can use a temp table:

USE AdventureWorks;
GO

SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
INTO #Sales
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM #Sales
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

Open in new window


Or you can reformat the CTE as an inner query (which is actually similiar to what SQL is doing):

USE AdventureWorks;
GO

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM (
       SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
         FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
     ) s  -- give it a table alias
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

Open in new window


The second one is more akin to what the WITH CTE expression represents in your example - the main advantage of a CTE is that you coudl reference it multiple times, in different subqueries, and only have it run once. 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.
0
 
Mark WillsTopic AdvisorCommented:
You can do it as a straight query:

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY Year(OrderDate), SalesPersonID
ORDER BY 1,3

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Mark WillsTopic AdvisorCommented:
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 ?

0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

HA ha ha ha.....

very Nice one mark sir....very nice one....
0
 
Mark WillsTopic AdvisorCommented:
:) 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 :)
0
 
enrique_aeoAuthor Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:
Well a CTE is a common table expression.

Like the subquery exccept you essentially give it a name which you can then subsequently refer to. That in itself is nothing great, except, what you can subseqently do. Because the base query becomes instantiated you can use it as you go. The subquery cannot.

Most obvious example of the fundamental advantage of a CTE is recursion. So traversing a tree or heirachy is a lot easier to achieve because for each parent node you can refer to its child nodes and for each child, refer to subsequent levels and so on. The data itself determines the number of layers and how deep each branch becomes. That is especially hard to do without the CTE.

Another more common example involves the window functions such as row_number. Not just a straight forward row_number() but something a bit more involved....

Such as deleting duplicates when there is nothing really to differentiate rows - how does one easily keep one instance of a duolicate if there is nothing easy to identify a row ? Simple, use the CTE to delete rows from the underlying table.

Then there are things like using "virtual" data calculated by CTE to then get table bound data. Things like a counter / date those types of easy to calculate objects can readily be achieved with the CTE.

Below are a few examples of what I am saying above. And these are simply examples, not good code or anything. The CTE is very flexible and easy to abuse so you do need to be mindful as to how and where you are using and what you are using it for...

Please also note these are not necessarily good things to do, but answers your secondary question above with examples. (which by the way is now quite a different question from your original posting).

-- create a dummy table with a range of duplicate values
if object_id('tempdb..#tmp') is not null drop table #tmp
GO

create table #tmp (cola int, colb int, coldate datetime)
GO

insert #tmp values (1,1,'20110101')
insert #tmp values (1,2,'20110102')
insert #tmp values (1,3,'20110301')
insert #tmp values (1,3,'20110301')
insert #tmp values (1,3,'20110301')
insert #tmp values (2,1,'20110101')
insert #tmp values (2,2,'20110102')
insert #tmp values (3,1,'20110331')
insert #tmp values (4,1,'20110102')
insert #tmp values (1,1,'20110101')
insert #tmp values (1,2,'20110102')
insert #tmp values (1,3,'20110301')
insert #tmp values (1,3,'20110301')
insert #tmp values (1,3,'20110301')
insert #tmp values (2,1,'20110101')
insert #tmp values (2,2,'20110102')
insert #tmp values (3,1,'20110331')
insert #tmp values (4,1,'20110102')
insert #tmp values (1,1,'20110101')
insert #tmp values (1,2,'20110102')
insert #tmp values (1,3,'20110301')
insert #tmp values (1,3,'20110301')
insert #tmp values (1,3,'20110301')
insert #tmp values (2,1,'20110101')
insert #tmp values (2,2,'20110102')
insert #tmp values (3,1,'20110331')
insert #tmp values (4,1,'20110102')

-- first example, use derived values to make a dynamic calendar

;with my_cte as
( select convert(datetime,'20110101') as The_Date
  union all
  select dateadd(day,1,the_date)
  from my_cte
  where the_date < '20110131'
)
select The_Date, count(colb) as daily_activity
from my_cte
left join #tmp on the_date = coldate
group by The_Date
go

-- use the CTE to nest the same CTE but with different qualifiers

;with 
a as
(select cola,colb, row_number() over (partition by cola order by colb desc) as rn from #tmp), 
b as
(select cola,colb, rn from a where rn = a.rn) 
select * from b

-- use the CTE to remove all but the highest value of colb and make sure there are no dupes

;with my_cte as
(select cola,colb, row_number() over (partition by cola order by colb desc) as rn from #tmp)
delete
from my_cte
where rn > 1
go

-- now let us check what is left

select * from #tmp

-- now cleanup

drop table #tmp

Open in new window






0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now