May 1, 2013 at 2:33 pm
I have a query i wrote for 2008 to get the top 3 salesPeople per territory by their total sales. Its not too difficult in 2005/2008 with CTE's and Partitioning however i cannot get this to work in SQL 2000 where there are no CTE's.
Id appreciate any help or nudges in the right direction:
Query is a mockup of the actual query, columns, tables and data is all fictional:
with ST AS
(select
ROW_NUMBER() over(partition by TerritoryID order by sum(totalDue) desc) as rowID,
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where SalesPersonID is not null
group by TerritoryID, SalesPersonID
)
select
territoryID
, salesPersonID
, SalesTotal
from ST
where RowID <=3
order by territoryID
Thanks!
May 1, 2013 at 2:50 pm
wow it's been a long time since i had to do this in SQL2000;
SQL 2000 doesn't support row_number() functions either.
if you can go without row_number, it just becomes a sub select:
select
territoryID
, salesPersonID
, SalesTotal
from (select
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where SalesPersonID is not null
group by TerritoryID, SalesPersonID
) ST
--where RowID <=3
order by territoryID
if you need row_number, it's going to require a tempt table, and updating the temp table with a neat trick i learned form Jeff Moden; unfortunately, it cannot be done in a single statement, either.
this is untested, since i don't have the sample data, but this should be kind of close:
--===== Create a test table.
-- This is NOT part of the solution.
IF OBJECT_ID('TempDB..#ST') IS NOT NULL
DROP TABLE #ST
--create the temp table, with placeholders for the partition by() of the row_number
select
identity(int,1,1) AS ID,
0 As rowID,
territoryID,
salesPersonID
, SalesTotal
INTO #ST
from (select
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where SalesPersonID is not null
group by TerritoryID, SalesPersonID ) ST
ORDER BY TerritoryID, SalesPersonID
--we need a clustered index
ALTER TABLE #ST
ADD PRIMARY KEY CLUSTERED (TerritoryID)
DECLARE @PrevTerritoryID INT,
@PrevSeq INT
--update the table and the variable/counter all in the same statemetn.
UPDATE #ST
SET @PrevSeq = rowid = CASE WHEN TerritoryID = @PrevTerritoryID THEN @PrevSeq + 1 ELSE 1 END,
@PrevTerritoryID = TerritoryID
FROM #ST WITH(INDEX(0),TABLOCKX)
--check the results?
SELECT * FROM #ST
--WHERE rowID <=3
ORDER BY TerritoryID
Lowell
May 1, 2013 at 6:09 pm
There is a simpler way.
No updates required:
--===== Create a test table.
-- This is NOT part of the solution.
IF OBJECT_ID('TempDB..#ST') IS NOT NULL DROP TABLE #ST
DECLARE @N int
SET @N = 3 -- how many rows to retrieve for each group
--create the temp table, with Identity column playing the role of RowNumber
select
identity(int,1,1) AS ID,
territoryID, salesPersonID, SalesTotal
INTO #ST
from (select
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where SalesPersonID is not null
group by TerritoryID, SalesPersonID ) ST
ORDER BY TerritoryID,
salesTotal DESC -- arranging ID's within the each group
SELECT T.*
FROM #ST T
INNER JOIN (
SELECT TerritoryID,
MIN(ID) FromID, MIN(ID) + @N ToID
--MIN(ID) is a starting point for rowcount within each group
FROM #ST
GROUP BY TerritoryID
) DT ON DT.TerritoryID = T.TerritoryID
AND T.ID BETWEEN DT.FromID AND DT.ToID
ORDER BY T.TerritoryID, T.ID
_____________
Code for TallyGenerator
May 2, 2013 at 12:32 pm
I think this will do it; performance will depend on the indexes available on the table:
select sales.*
from (
select distinct TerritoryID
from [Sales].[SalesOrderHeader]
) AS terrs
inner join
(select top 3
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where
TerritoryID = terrs.TerritoryID and
SalesPersonID is not null
group by TerritoryID, SalesPersonID
order by sum(TotalDue) DESC
) AS sales on
sales.TerrorityID = terrs.TerritoryID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply