Can anyone help me with this? Its giving me a bad headache!

Hi All,

I have a table called Prizes. Here's how it looks in design view with some value placed inside for Illustration purposes.

PrizeID 1, 2, 3, 4, 5
PromotionID 1, 1, 1, 2, 1
PrizeName 10 Cash, 5 cash, 10 cash, 15 cash, 20 cash

My challenge is that I need to write a stored procedure for example, that will find the PrizeID associated with the 4th count of the PromotionID that equals 1 . So in this example, counting to the 4th PromotionID that equalls 1 give us a PrizeID of 5.

I hope I've made myself clear! Can anyone write out a mini SP on how to do this.

Many many thanks in advance,
BradTry creating a temporary table that has an identity column(tempID), a column for PrizeID. Then use an insert statement that selects from Prizes where PromotionID=1 and order it by PrizeID ASC. Then, selecting from your temporary table where tempID=4 (or whatever count you are looking for) will return the corresponding PrizeID. The query to populate the temporary table should look something like this...

DECLARE @.temp TABLE(tempID INT IDENTITY(1,1) PRIMARY KEY,PrizeID INT);

INSERT INTO @.temp (PrizeID) SELECT PrizeID FROM Prizes WHERE PromotionID=1 ORDER BY PrizeID ASC;|||Wow, thank you! I'll give it a try tonight.

Brad