Adding a counter field based on another number field

Question

Adding a counter field based on another number field

Mike Chiodo

Grasshopper

Points: 14
More actions
January 13, 2009 at 1:07 pm

#126602

Hi,
I have a table that contains about 200,000 records of check information. There might be 5 records for chk_no 1234, 4 records for chk_no 1235, 6 records for chk_no 1236 and 3 records for chk_no 1237. Each line has a different benefit code.
I'd like to add a field (count_id) to start counting from one at each change of chk_no.
Any suggestions or pointers? I'm new at SQL and programming.
My table would look like this:
chk_no ben_code count_id
1234 medc 1
1234 ss 2
1234 fica 3
1234 hlth 4
1235 medc 1
1235 ss 2
1235 fica 3
1235 hlth 4
1235 dent 5
1236 medc 1
1236 ss 2
1236 fica 3
1236 hlth 4
1237 medc 1
1237 ss 2
1237 fica 3
1237 hlth 4
1237 dent 5
1237 csup 6
Thanks!

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

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

Lowell SSC Guru Points: 323755 More actions · Answer 1

i would not put the counter_id data in the table...instead i would make a view which had the value you were looking for:

[font="Courier New"]

SELECT Row_number() OVER (PARTITION BY x.chk_no ORDER BY x.chk_no),X.*

FROM (

SELECT 1234 AS chk_no,'medc' AS ben_code UNION

SELECT 1234,'ss' UNION

SELECT 1234,'fica' UNION

SELECT 1234,'hlth' UNION

SELECT 1235,'medc' UNION

SELECT 1235,'ss' UNION

SELECT 1235,'fica' UNION

SELECT 1235,'hlth' UNION

SELECT 1235,'dent' UNION

SELECT 1236,'medc' UNION

SELECT 1236,'ss' UNION

SELECT 1236,'fica' UNION

SELECT 1236,'hlth' UNION

SELECT 1237,'medc' UNION

SELECT 1237,'ss' UNION

SELECT 1237,'fica' UNION

SELECT 1237,'hlth' UNION

SELECT 1237,'dent' UNION

SELECT 1237,'csup') X

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mike Chiodo Grasshopper Points: 14 More actions · Answer 2

Lowell (1/13/2009)
i would not put the counter_id data in the table...instead i would make a view which had the value you were looking for:
[font="Courier New"]
SELECT Row_number() OVER (PARTITION BY x.chk_no ORDER BY x.chk_no),X.*
FROM (
SELECT 1234 AS chk_no,'medc' AS ben_code UNION
SELECT 1234,'ss' UNION
SELECT 1234,'fica' UNION
SELECT 1234,'hlth' UNION
SELECT 1235,'medc' UNION
SELECT 1235,'ss' UNION
SELECT 1235,'fica' UNION
SELECT 1235,'hlth' UNION
SELECT 1235,'dent' UNION
SELECT 1236,'medc' UNION
SELECT 1236,'ss' UNION
SELECT 1236,'fica' UNION
SELECT 1236,'hlth' UNION
SELECT 1237,'medc' UNION
SELECT 1237,'ss' UNION
SELECT 1237,'fica' UNION
SELECT 1237,'hlth' UNION
SELECT 1237,'dent' UNION
SELECT 1237,'csup') X
[/font]

I have no choice. The counter field has to be added to the table.