Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server: As I can do it without using CTE expression

Posted on 2011-03-14
8
Medium Priority
?
570 Views
Last Modified: 2012-05-11
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
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35135384
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
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 668 total points
ID: 35135395
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1332 total points
ID: 35135547
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 35135588
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35135591
Hi,

HA ha ha ha.....

very Nice one mark sir....very nice one....
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35135618
:) 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
 

Author Comment

by:enrique_aeo
ID: 35153591
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1332 total points
ID: 35154857
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question