Solved

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

Posted on 2011-03-14
8
543 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 167 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 333 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 333 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

710 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