for each in a query on SQL 2000

Question

for each in a query on SQL 2000

wilburb

Newbie

Points: 9
More actions
December 28, 2010 at 11:57 am

#97790

I know this is an older version of SQL, but I am trying to simplify some year end reporting and need to figure out how to do this. the data structure is pretty straight forward. There are multiple locations selling products. I want to know the top n products sold at each location. Like others, I can get the top n but not at each location (without adding their location in the where clause and then running it n number of times). I am including the query for the top n in hopes someone can tell me how to run the same query and get only the top n for each location.
SELECT TOP n t.StoreId, td.InventoryId, SUM(td.Quantity)
FROM tblTransaction t INNER JOIN tblTransactionDetail td ON t.TransactionId = td.TransactionId
WHERE t.TransactionDate BETWEEN 'some date' AND 'another date'
GROUP BY t.StoreId, td.InventoryId
ORDER BY t.StoreId, SUM(td.Quantity) DESC
So far, I have added another condition to the WHERE line that is t.StoreId = 'locationid' and then run this for each location. Can be very time consuming.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I think for SQL 2000, I'd do it with a loop. This is pretty much what the ranking functions and Apply were added to SQL 2005 to solve.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

Heh... no need to curse(or) in public, Gus... 😛

Here's a real old example of how to do it in SQL Server 2000... if you want code for your data, please post the table and data IAW the article at the first link in my signature line below after the code below.

/**************************************************************

Select the top x from each group

**************************************************************/

--===== Suppress auto-display of line counts for speed

SET NOCOUNT ON

--===== If temp testing table exists, drop it

IF OBJECT_ID('TEMPDB..#MyTemp') IS NOT NULL

DROP TABLE #MyTemp

--===== Create the temp testing table

CREATE TABLE #MyTemp

(

[Name] VARCHAR(10),

[Date] DateTime DEFAULT GETDATE()

)

INSERT INTO #MyTemp ([Name], [Date]) VALUES('Arley','20040302')

INSERT INTO #MyTemp ([Name], [Date]) VALUES('Arley','20040310')

INSERT INTO #MyTemp ([Name], [Date]) VALUES('Arley','20040312')

INSERT INTO #MyTemp ([Name], [Date]) VALUES('Arley','20040301')

INSERT INTO #MyTemp ([Name], [Date]) VALUES('Frank','20040310')

INSERT INTO #MyTemp ([Name], [Date]) VALUES('Ben','20040317')

INSERT INTO #MyTemp ([Name], [Date]) VALUES('Ben','20040316')

INSERT INTO #MyTemp ([Name], [Date]) VALUES('Ben','20040315')

--===== Demo the answer

SELECT T1.[Name],

T1.[Date]

FROM #MyTemp T1

WHERE T1.[Date] IN

(

SELECT TOP 2 --Change this number to vary # output

T2.[Date]

FROM #MyTemp T2

WHERE T2.[Name] = T1.[Name]

ORDER BY T2.[Date] DESC

)

ORDER BY T1.[Name],

T1.[Date] DESC

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)