SUM or COUNT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SUM or COUNT

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-12-22 : 14:55:28

I am trying to replicate some work in SQL that I do with an Excel Pivot Table.

Here is my SQL code:

SELECT losttime.CTwentyOne.BranchCode,
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'YES'
WHEN losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate THEN 'YES'
ELSE 'NO'
END AS C23Due,
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2002 THEN 1 ELSE 0 END "2002",
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2003 THEN 1 ELSE 0 END "2003"
FROM losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and (losttime.CTwentyOne.AbsenceCode = 'NC'
or losttime.CTwentyOne.AbsenceCode = 'PO'
or losttime.CTwentyOne.AbsenceCode = 'RL'
or losttime.CTwentyOne.AbsenceCode = 'SK'
or losttime.CTwentyOne.AbsenceCode = 'SKL'
or losttime.CTwentyOne.AbsenceCode = 'SKP'
or losttime.CTwentyOne.AbsenceCode = 'SKQ'
or losttime.CTwentyOne.AbsenceCode = 'SKU'
or losttime.CTwentyOne.AbsenceCode = 'UA')
and losttime.Profit_Centers_Table.AirportCode = 'DFW'
and losttime.Branches_Table.BranchCategorySmall = 'RSVC'
and losttime.CTwentyOne.AbsenceStartDate >= '05/01/2003'
and losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field'
GROUP BY losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.C23DiscussionDate,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate
HAVING SUM(losttime.CTwentyOne.SKL_POINTS
+ losttime.CTwentyOne.NC_POINTS
+ losttime.CTwentyOne.PO_POINTS
+ losttime.CTwentyOne.RL_POINTS
+ losttime.CTwentyOne.SK_POINTS
+ losttime.CTwentyOne.SKP_POINTS
+ losttime.CTwentyOne.SKQ_POINTS
+ losttime.CTwentyOne.SKU_POINTS
+ losttime.CTwentyOne.UA_POINTS) >= 2.5
ORDER BY losttime.CTwentyOne.BranchCode

Here are the Results:

BRA/CDE C23 2002 2002
4510 YES 0 1
4510 YES 0 1
4510 YES 0 1
4510 YES 0 1
4510 YES 0 1
4510 YES 0 1
4510 YES 0 1
4510 YES 0 1
4510 NO 0 1
4510 YES 0 1
4510 NO 0 1
4510 NO 0 1
4510 NO 0 1
4510 NO 0 1
4510 NO 0 1
4510 NO 0 1
4510 YES 0 1
4510 NO 0 1
4510 NO 0 1
4510 YES 0 1
4510 NO 0 1
4510 YES 0 1
4510 NO 0 1
4510 YES 0 1
4510 YES 0 1
4510 NO 0 1
4510 NO 0 1
4510 NO 0 1
4510 NO 0 1
4510 NO 0 1
4810 YES 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 YES 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 YES 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4810 NO 0 1
4820 YES 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 YES 0 1
4820 NO 0 1
4820 NO 0 1
4820 YES 0 1
4820 YES 0 1
4820 NO 0 1
4820 NO 0 1
4820 YES 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1
4820 NO 0 1

I've been trying to get the results to resemble a pivot table result where everything is totaled or Rolled Up as:

Bra/Cde C23 2002 2003
4510 Yes 0 44
4510 NO 0 33
4810 Yes 0 26
4810 NO 0 10
4820 Yes 0 16
4820 NO 0 03

In the pivot table I have it perform a COUNT function of the EmployeeNumber.

I place the BranchCode and C23 on the left and the Month / date info at the top.

Thanks for your help as always and Happy Holidays.

GC

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-22 : 15:24:44

Here is a method which places your existing query into a #table ( #Pivot)
and performs a union of Yes results and No results:

SELECT losttime.CTwentyOne.BranchCode,
CASE 
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'YES' 
WHEN losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate THEN 'YES' 
ELSE 'NO' 
END AS C23Due,
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2002 THEN 1 ELSE 0 END "2002",
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2003 THEN 1 ELSE 0 END "2003"

INTO #Pivot

FROM losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode 
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and (losttime.CTwentyOne.AbsenceCode = 'NC'
or losttime.CTwentyOne.AbsenceCode = 'PO'
or losttime.CTwentyOne.AbsenceCode = 'RL'
or losttime.CTwentyOne.AbsenceCode = 'SK'
or losttime.CTwentyOne.AbsenceCode = 'SKL'
or losttime.CTwentyOne.AbsenceCode = 'SKP'
or losttime.CTwentyOne.AbsenceCode = 'SKQ'
or losttime.CTwentyOne.AbsenceCode = 'SKU'
or losttime.CTwentyOne.AbsenceCode = 'UA')
and losttime.Profit_Centers_Table.AirportCode = 'DFW'
and losttime.Branches_Table.BranchCategorySmall = 'RSVC'
and losttime.CTwentyOne.AbsenceStartDate >= '05/01/2003'
and losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field'
GROUP BY losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.C23DiscussionDate,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate 
HAVING SUM(losttime.CTwentyOne.SKL_POINTS
+ losttime.CTwentyOne.NC_POINTS
+ losttime.CTwentyOne.PO_POINTS
+ losttime.CTwentyOne.RL_POINTS 
+ losttime.CTwentyOne.SK_POINTS
+ losttime.CTwentyOne.SKP_POINTS
+ losttime.CTwentyOne.SKQ_POINTS
+ losttime.CTwentyOne.SKU_POINTS
+ losttime.CTwentyOne.UA_POINTS) >= 2.5
ORDER BY losttime.CTwentyOne.BranchCode

	
SELECT BranchCode [BRA/CDE], 'Yes' C23, count([2002]) [2002], count([2003]) [2003]
FROM
(
	SELECT BranchCode, C23Due, [2002], [2003]
	FROM #Pivot
) a
WHERE C23 = 'Yes'
GROUP BY BranchCode

UNION	

SELECT BranchCode [BRA/CDE], 'No' C23, count([2002]) [2002], count([2003]) [2003]
FROM
(
	SELECT BranchCode, C23Due, [2002], [2003]
	FROM #Pivot
) a
WHERE C23 = 'No'
GROUP BY BranchCode

DROP TABLE #Pivot

EDIT: Sam has the preferred (GROUP BY) solution below.
Nice Job Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-22 : 15:35:42

Looks like your query could be shortened using the 'IN' operator.

SELECT losttime.CTwentyOne.BranchCode,
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'YES'
WHEN losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate THEN 'YES'
ELSE 'NO'
END AS C23Due,
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2002 THEN 1 ELSE 0 END "2002",
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2003 THEN 1 ELSE 0 END "2003"
FROM losttime.CTwentyOne JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and losttime.CTwentyOne.AbsenceCode IN ('NC', 'PO', 'RL', 'SK', 'SKL', 'SKP', 'SKQ', 'SKU', 'UA')
and losttime.Profit_Centers_Table.AirportCode = 'DFW'
and losttime.Branches_Table.BranchCategorySmall = 'RSVC'
and losttime.CTwentyOne.AbsenceStartDate >= '05/01/2003'
and losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field'
GROUP BY losttime.CTwentyOne.BranchCode, losttime.CTwentyOne.C23DiscussionDate, losttime.CTwentyOne.AbsenceStartDate, losttime.CTwentyOne.AbsenceStopDate
HAVING SUM(losttime.CTwentyOne.SKL_POINTS
+ losttime.CTwentyOne.NC_POINTS
+ losttime.CTwentyOne.PO_POINTS
+ losttime.CTwentyOne.RL_POINTS
+ losttime.CTwentyOne.SK_POINTS
+ losttime.CTwentyOne.SKP_POINTS
+ losttime.CTwentyOne.SKQ_POINTS
+ losttime.CTwentyOne.SKU_POINTS
+ losttime.CTwentyOne.UA_POINTS) >= 2.5
ORDER BY losttime.CTwentyOne.BranchCode

It isn't necessary to explicitly qualify every column like "losttime.CTwentyOne.NC_Points", shorten it to "NC_Points" and it will do fine. If it's ambiguous, then you can use an alias like:

FROM losttime.CTwentyOne L JOIN ....

...

HAVING SUM(L.SKL_POINTS + L.NC_POINTS + L.PO_POINTS + L.RL_POINTS + L.SK_POINTS + L.SKP_POINTS + L.SKQ_POINTS + L.SKU_POINTS + L.UA_POINTS) >= 2.5

To get the resultset you want, another GROUP BY should do it

SELECT BranchCode, C23Due, SUM([2002]) as '2002', SUM([2003]) AS '2003'
FROM (
-- INSERT your query here
) X
GROUP BY BranchCode, C23Due
ORDER BY BranchCode, C23Due

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-12-22 : 15:50:33

ehorn,

I gave it a try and it works but doesn't really give me the results I'm looking for. I don't think it is picking up the logic and instead is just counting.

Here is what I get.

Bra/CDE C23Due 2002 2003
4510 No 874 874
4810 No 51 51
4820 No 23 23
4820 Yes 5 5
4510 Yes 237 237
4810 Yes 15 15

GC

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-22 : 15:55:59

quote:
Originally posted by Gary Costigan

ehorn,

I gave it a try and it works but doesn't really give me the results I'm looking for. I don't think it is picking up the logic and instead is just counting.

Here is what I get.

Bra/CDE C23Due 2002 2003
4510 No 874 874
4810 No 51 51
4820 No 23 23
4820 Yes 5 5
4510 Yes 237 237
4810 Yes 15 15

GC

I think there was an error in the WHERE criteria. It should be:


SELECT BranchCode [BRA/CDE], 'Yes' C23, count([2002]) [2002], count([2003]) [2003]
FROM
(
	SELECT BranchCode, C23Due, [2002], [2003]
	FROM #Pivot
) a
WHERE C23Due = 'Yes'
GROUP BY BranchCode

UNION	

SELECT BranchCode [BRA/CDE], 'No' C23, count([2002]) [2002], count([2003]) [2003]
FROM
(
	SELECT BranchCode, C23Due, [2002], [2003]
	FROM #Pivot
) a
WHERE C23Due = 'No'
GROUP BY BranchCode

Take a look at Sams method. It should work for you and is much more efficient as it performs the count in one step.

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-12-22 : 16:00:36

Here is what I tried and now am getting all kinds of error msgs.

SELECT losttime.CTwentyOne.BranchCode,
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'YES'
WHEN losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate THEN 'YES'
ELSE 'NO'
END AS C23Due,
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2002 THEN 1 ELSE 0 END "2002",
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2003 THEN 1 ELSE 0 END "2003"
FROM losttime.CTwentyOne JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and losttime.CTwentyOne.AbsenceCode IN ('NC', 'PO', 'RL', 'SK', 'SKL', 'SKP', 'SKQ', 'SKU', 'UA')
and losttime.Profit_Centers_Table.AirportCode = 'DFW'
and losttime.Branches_Table.BranchCategorySmall = 'RSVC'
and losttime.CTwentyOne.AbsenceStartDate >= '05/01/2003'
and losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field'
GROUP BY losttime.CTwentyOne.BranchCode, losttime.CTwentyOne.C23DiscussionDate, losttime.CTwentyOne.AbsenceStartDate, losttime.CTwentyOne.AbsenceStopDate
HAVING SUM(losttime.CTwentyOne.SKL_POINTS
+ losttime.CTwentyOne.NC_POINTS
+ losttime.CTwentyOne.PO_POINTS
+ losttime.CTwentyOne.RL_POINTS
+ losttime.CTwentyOne.SK_POINTS
+ losttime.CTwentyOne.SKP_POINTS
+ losttime.CTwentyOne.SKQ_POINTS
+ losttime.CTwentyOne.SKU_POINTS
+ losttime.CTwentyOne.UA_POINTS) >= 2.5
ORDER BY losttime.CTwentyOne.BranchCode

SELECT BranchCode, C23Due, SUM([2002]) as '2002', SUM([2003]) AS '2003'
FROM losttime.CTwentyOne JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and losttime.CTwentyOne.AbsenceCode IN ('NC', 'PO', 'RL', 'SK', 'SKL', 'SKP', 'SKQ', 'SKU', 'UA')
and losttime.Profit_Centers_Table.AirportCode = 'DFW'
and losttime.Branches_Table.BranchCategorySmall = 'RSVC'
and losttime.CTwentyOne.AbsenceStartDate >= '05/01/2003'
and losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field'
GROUP BY BranchCode, C23Due
ORDER BY BranchCode, C23Due

Here are the errors.

Server: Msg 207, Level 16, State 3, Line 32
Invalid column name 'C23Due'.
Server: Msg 207, Level 16, State 1, Line 32
Invalid column name '2002'.
Server: Msg 207, Level 16, State 1, Line 32
Invalid column name 'C23Due'.
Server: Msg 207, Level 16, State 1, Line 32
Invalid column name '2003'.
Server: Msg 207, Level 16, State 1, Line 32
Invalid column name 'C23Due'.
Server: Msg 209, Level 16, State 1, Line 32
Ambiguous column name 'BranchCode'.
Server: Msg 209, Level 16, State 1, Line 32
Ambiguous column name 'BranchCode'.
Server: Msg 209, Level 16, State 1, Line 32
Ambiguous column name 'BranchCode'.

Thanks again.

GC

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-22 : 16:06:58

Gary,

Cut and paste this and then post any errors.

SELECT BranchCode, C23Due, SUM([2002]) as '2002', SUM([2003]) AS '2003'
FROM (
SELECT losttime.CTwentyOne.BranchCode,
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'YES'
WHEN losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate THEN 'YES'
ELSE 'NO'
END AS C23Due,
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2002 THEN 1 ELSE 0 END "2002",
CASE DATEPART(YY,losttime.CTwentyOne.AbsenceStartDate) WHEN 2003 THEN 1 ELSE 0 END "2003"
FROM losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and (losttime.CTwentyOne.AbsenceCode = 'NC'
or losttime.CTwentyOne.AbsenceCode = 'PO'
or losttime.CTwentyOne.AbsenceCode = 'RL'
or losttime.CTwentyOne.AbsenceCode = 'SK'
or losttime.CTwentyOne.AbsenceCode = 'SKL'
or losttime.CTwentyOne.AbsenceCode = 'SKP'
or losttime.CTwentyOne.AbsenceCode = 'SKQ'
or losttime.CTwentyOne.AbsenceCode = 'SKU'
or losttime.CTwentyOne.AbsenceCode = 'UA')
and losttime.Profit_Centers_Table.AirportCode = 'DFW'
and losttime.Branches_Table.BranchCategorySmall = 'RSVC'
and losttime.CTwentyOne.AbsenceStartDate >= '05/01/2003'
and losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field'
GROUP BY losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.C23DiscussionDate,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate
HAVING SUM(losttime.CTwentyOne.SKL_POINTS
+ losttime.CTwentyOne.NC_POINTS
+ losttime.CTwentyOne.PO_POINTS
+ losttime.CTwentyOne.RL_POINTS
+ losttime.CTwentyOne.SK_POINTS
+ losttime.CTwentyOne.SKP_POINTS
+ losttime.CTwentyOne.SKQ_POINTS
+ losttime.CTwentyOne.SKU_POINTS
+ losttime.CTwentyOne.UA_POINTS) >= 2.5
) X
GROUP BY BranchCode, C23Due
ORDER BY BranchCode, C23Due

Note that the ORDER BY was removed from the inner red query. SQL complains a lot about ordering subqueries.

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-12-22 : 16:11:36

SAMC,

Bingo, worked like a dream.

Bra/CDe C23Due 2002 2003
4510 NO 0 874
4510 YES 0 237
4810 NO 0 51
4810 YES 0 15
4820 NO 0 23
4820 YES 0 5

Thanks for all your help to you and ehorn.

Merry Christmas,

GC

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-22 : 16:15:05

quote:
Originally posted by SamC

Note that the ORDER BY was removed from the inner red query. SQL complains a lot about ordering subqueries.

Sam,

A friend lent me Ken Hendersons Transact SQL book this weekend and I was not aware that you can keep order by in derived tables if you trick it with a top n clause in the derived table ie.

select * 
from
(
select top 100 percent <somecolumns> 
from <sometable>
order by <somecolumn>
) a

I thought this was pretty cool.

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-22 : 16:41:04

It is a very cool trick, mandatory for finding Top N rows in a derived table.

I've managed to rewrite all my TOP 100 percent subqueries by moving the ORDER BY to the outermost select. I'm sure there are reasons but I haven't run into one in a while now. I probably have a stored procedure in my DB using the feature right now but I have no idea where it is.

Query Analyzer needs a "FIND" feature similar to Microsoft Word's FIND. Something like FIND 'top 100' IN *.*

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources