Record count column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Record count column

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-08-09 : 14:24:39

Hello All,

I have a stored procedure which inserts the data in #temp table. I need to be able to add the record count in one of the column of the temp table. The RowID column has to reset to one and then should increment by 1 until new ID value is found.

For example:

ID PosID RowID
201 12 1
201 13 2
201 16 3
202 08 1
202 10 2
203 11 1
203 14 2

Can anyone tell me how to update the code in my SP to implement the above change?

Thanks in advace,
P

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-09 : 14:57:32

INSERT INTO #TEMP
select ID,PosID,RANK() OVER (partition by ID order by posid)

from yourtable

Jim

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-09 : 15:06:25

Or one way in SQL 2000:

DECLARE @Source TABLE (ID INT, PosID INT)

INSERT @Source
SELECT 201, 12
UNION ALL SELECT 201, 13
UNION ALL SELECT 201, 16
UNION ALL SELECT 202, 08
UNION ALL SELECT 202, 10
UNION ALL SELECT 203, 11
UNION ALL SELECT 203, 14


DECLARE @Target TABLE(ID INT, PosID INT, RowID INT)

INSERT @Target (ID, PosID, RowID)
SELECT
	S.ID,
	S.PosID,
	(SELECT COUNT(*) FROM @Source AS S1 WHERE S.ID = S1.ID AND S1.PosID < S.PosID)  + 1 AS RowID
FROM
	@Source AS S
ORDER BY
	S.ID,
	S.PosID

SELECT *
FROM @Target

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-08-10 : 10:43:06

Thanks Jim and Lamprey for the quick reply.

I tried Jim's solution but got did not get it to work (Is it because I have sql 2000 not 2005?)
Then I tried Lamprey's suggestion, it worked the way I wanted it. But what if the S1.PosID is not small thaan the S.PosID? Are you doing the order by just to keep PosID in order So that the S1.PosID will always less than S.PosID?

Thanks,
-P

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-10 : 11:37:21

Sorry, sqlpal2007, my solution is for SQL 2005, I didn't pay attention to the forum I was in (great looking solution though, isn't it?!). The ORDER BY clause in Lamprey's solution is just to display the records in a certain order, and doesn't affect the calculations. Look at lamprey's solution a little closer, it's quite clever and handles the situation where s1.posid is not < s.posid

SELECT COUNT(*) FROM @Source AS S1 WHERE S.ID = S1.ID AND S1.PosID < S.PosID) + 1

Jim

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-10 : 13:04:15

The only thing that would cause an issue is if an ID had two or more PosIDs that were the same. If that is not an issue, then the above query shold work.

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-08-10 : 15:02:26

Jim and Lampery,

Thanks for the reply. Jim, your reply I will keep in mind when we migrate to 2005 which is just in couple of months. I read help on the RANK() function. It is really nice feature.

Lampery, PosID will be always unique, The values will never duplicate. I already implemented your suggestion. :)

Thanks guys for helping me out
-P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources