Looping in T-SQL

Question

Looping in T-SQL

Piet van der Westhuizen

Valued Member

Points: 50
More actions
September 26, 2007 at 6:07 am

#175159

Hallo
I have a table(tblFactories) that lists all my factories and another table that lists transactions (tblTrans) for all my factories.
I need to extract top 6 transactions for each factory in tblFactories from all transactions in tblTrans
somehting like -
for each factory in tblFactories [current factory]
insert tblTop6
select top 6 from tblTrans
where factory = [current factory]
Thank you.

Viewing 8 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 1

You can certainly loop if you want... but let's try to avoid that in a relational database if we can. 😉

You say you want the top 6 transactions for each factory, but, in a relational database, the order of rows is no necessarily guaranteed. In order to help you, you have to identify what you mean by "top 6"... do you want the most recent transactions, the transactions worth the most money, the trasactions worth the least money, or ???

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

First you want to get away from procedural thinking. SQL works best set based. Also, post the DDL for your tables, it makes it easier for people to respond with complete answers.

You can accomplish what you want by using a CTE (Common Table Expression) and the Row_Number() function.

I’m assuming here that what determines what is in the TOP 6 is the transaction date, but that is only an assumption.

WITH transactionsCTE

AS (SELECT

factoryID

,ROW_NUMBER() OVER (PARTITION BY factoryID ORDER BY transactionDate) AS rn

,transactionField1

,transactionField2

,transactionField3

FROM

transactionsTable)

INSERT tblTop6

SELECT

factoryID

,transactionField1

,transactionField2

,transactionField3

FROM

transactionsCTE

WHERE

rn < 7

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Piet van der Westhuizen Valued Member Points: 50 More actions · Answer 3

OK I made it sound very simple.

My data looks like this in tblTransactions (all hypothetical) 🙂

Factory Item PurchasePeriod Spend

=============================

Fact1 101 1 10

Fact2 103 3 5

Fact1 101 2 5

Fact2 104 3 20

ETC.....

I need to run a query that gives me the top 6 spend items for each factory.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

Change "transactionDate" to "Spend DESC" in my code and you should get what you need.:D

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 5

Piet van der Westhuizen (9/26/2007)
OK I made it sound very simple.
My data looks like this in tblTransactions (all hypothetical) 🙂
Factory Item PurchasePeriod Spend
=============================
Fact1 101 1 10
Fact2 103 3 5
Fact1 101 2 5
Fact2 104 3 20
ETC.....
I need to run a query that gives me the top 6 spend items for each factory.

That's nice... but you still haven't identified the columns that allow you to determine an ORDER BY at to what the TOP 6 for any given factory is. Or, do you just want something random (probably not...)?

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

But I like random, it's rather difficult to disprove random results... :hehe:

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Piet van der Westhuizen Valued Member Points: 50 More actions · Answer 7

WITH transactionsCTE
AS (SELECT
factoryID
,ROW_NUMBER() OVER (PARTITION BY factoryID ORDER BY transactionDate) AS rn
,transactionField1
,transactionField2
,transactionField3
FROM
transactionsTable)
INSERT tblTop6
SELECT
factoryID
,transactionField1
,transactionField2
,transactionField3
FROM
transactionsCTE
WHERE
rn < 7

This does the job for me, thanks guys!