Solved

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

Posted on 2011-03-14
8
502 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
8 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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 167 total points
Comment Utility
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 333 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Hi,

HA ha ha ha.....

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

Expert Comment

by:Mark Wills
Comment Utility
:) 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
Comment Utility
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 333 total points
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard 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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now