adding a auto incrementing col to result set

[jcisco2]

i have a question what i'm trying to do is add a incrementing col to a resulting set for example i have this query.

SELECT tblProducts.ProdID, tblProducts.ItemNbr, tblProducts.Category, tblProducts.ProdInfo
FROM tblProducts INNER JOIN
tblProdOrdrd ON tblProducts.ProdID = tblProdOrdrd.ProdID INNER JOIN
tbCust ON tblProdOrdrd.CustID = tbCust.ID
WHERE (tbCust.CustNbr = N'AFFAMA0')
GROUP BY tblProducts.ProdID, tblProducts.ItemNbr, tblProducts.Category, tblProducts.ProdInfo


and it returns data like this.
ProdID ItemNbr Category ProdInfo
379 07300 HD Cas
383 07303 HD Cas
415 07500 HD Cas


Now then what i would like to be able to do is add a col that increments itself so i can go back and forth between items in code. for example i would like something like this

ProdID ItemNbr Category ProdInfo ItemOrder
379 07300 HD Cas 1
383 07303 HD Cas 2
415 07500 HD Cas 3

then on my next select statement if they wanted to look at the next item i could loop through the resulting set and pull out the info for ITemOrder of 2 and display it's results. is there a way to do this?

 

[nigelrivett]

How are you displaying this?
If this is displaying to the user then you must have a presentation layer to do this. If you just want to display one rec at a time then it is easiest using the cursor in the presentation layer.

If you want to call an SP to get back a single row (an exccedingly ineffficient method) then you will need to order your resultset otherwise there is no guarantee that two calls will get data in the same order.

Do a search for paging - you just have a special case with a single row page.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jcisco2]

that's what i ended up doing. i had the presentation layer use a dataset (which i really didn't want to do). and i moved through that dataset with some buttons i created to display the records on my web page. I was just wondering if there was a way to have the resulting set come back with an Identity attached to it.


thanks!

cheers.

 

[PCJock]

If you don't mind using a stored procedure, you could try this code. I had to do this in a one of my applications for a different reason. You will need to change the table column types to match your fields.

SET NOCOUNT ON
DECLARE @MyTable table
	(
	UniqueId [int] IDENTITY(1,1) NOT NULL,
	ProdID int,
	ItemNbr int,
	Category int,
	ProdInfo int
	)

INSERT INTO @MyTable (ProdID,ItemNbr,Category,ProdInfo)
SELECT tblProducts.ProdID, tblProducts.ItemNbr, Products.Category, tblProducts.ProdInfo
FROM tblProducts 
INNER JOIN tblProdOrdrd 
ON tblProducts.ProdID = tblProdOrdrd.ProdID
INNER JOIN tbCust 
ON tblProdOrdrd.CustID = tbCust.ID
WHERE (tbCust.CustNbr = N'AFFAMA0')
GROUP BY tblProducts.ProdID, tblProducts.ItemNbr, tblProducts.Category, tblProducts.ProdInfo

SELECT * FROM @MyTable

 

[NathanGriffiths]

Theses FAQs might help:

faq183-3021 How do I get a row number on each row of a query result set?

faq183-207 How to renumber records, do running totals without a cursor



Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------