generating a grouped counter field

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

Site Related Forums

Article Discussion

generating a grouped counter field

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-12-05 : 12:43:27

Hi,

I thought the following task would be easy but I'm realizing that it's a bit tricky. I'm hoping someone can help me out.

What I've started is a query in the pubs database that lists all of
the employees with an ascending counter. Note that this counter is
generated dynamically:


USE pubs
GO

SELECT emp_id, fname, lname, job_id, 
   (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber 
FROM employee e 
ORDER BY lname 
GO

=====================================================
emp_id      fname       lname       job_id  rownumber
=====================================================
PMA42628M   Paolo       Accorti         13      1
PSA89086M   Pedro       Afonso          14      2
VPA30890F   Victoria    Ashworth        6       3
H-B39728F   Helen       Bennett         12      4
L-B31947F   Lesley      Brown           7       5
F-C16315M   Francisco   Chang           4       6
PTC11962M   Philip      Cramer          2       7
A-C71970F   Aria        Cruz            10      8
AMD15433F   Ann         Devon           3       9
...

What I'm trying to accomplish is to be able to group the employees
by job_id and have the counter start over at 1 with each new job_id
group as shown below:


=====================================================
emp_id      fname       lname       job_id  group_id
=====================================================
PTC11962M   Philip      Cramer          2       1
AMD15433F   Ann         Devon           3       1
F-C16315M   Francisco   Chang           4       1
PXH22250M   Paul        Henriot         5       1
CFH28514M   Carlos      Hernadez        5       2
JYL26161F   Janine      Labrune         5       3
LAL21447M   Laurence    Lebihan         5       4
RBM23061F   Rita        Muller          5       5
SKO22412M   Sven        Ottlieb         5       6
MJP25939M   Maria       Pontes          5       7
VPA30890F   Victoria    Ashworth        6       1
MGK44605M   Matti       Karttunen       6       2
DWR65030M   Diego       Roel            6       3
A-R89858F   Annette     Roulet          6       4
...

Like I said, I thought this would be easy but I'm stumped. Any ideas?

Bill

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-05 : 14:06:25

This has been discussed alot in the forums. In your case, you are 99%. Just group by JobID and add a criteria to your WHERE clause to make sure the subquery has the same jobID as the main query.

SELECT job_id, emp_id, fname, lname,
(SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname and e2.job_ID = e.job_Id) AS rownumber
FROM employee e
GROUP BY Job_Id
ORDER BY job_Id

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-05 : 14:08:45

Something I got from Rob,

Generate Sequence Order By Group

COLUMN_1 SEQUENCE_ID
-------- -----------
A NULL
A NULL
B NULL
B NULL
B NULL
C NULL
C NULL
C NULL
C NULL

What I want is the #temp table to look like this:

COLUMN_1 SEQUENCE_ID
-------- -----------
A 1
A 2
B 1 <- The sequence_id needs to be reset
B 2 to 1 when the row value in COLUMN_1
B 3 changes
C 1
C 2
C 3
C 4

Seeing from the above, the sequencing for SEQUENCE_ID is by COLUMN_1 aggregation.

DECLARE @col1 varchar(1), @seq int
SELECT @seq=0
UPDATE #temp
SET @seq = SEQUENCE_ID = CASE @col1 WHEN COLUMN_1 THEN @seq+1 ELSE 1 END,
@col1 = COLUMN_1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-05 : 14:09:45

Also, see

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22197

if you want to end up sorting this query by the rownumber; it uses a join instead of a subquery which lets your sort by your COUNT() field.

Boy, rankings and running totals are the most common topics it seems like .....

- Jeff

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-12-05 : 18:39:12

Excellent suggestions, guys! Since my table could have millions of rows I think the update solution would be faster than the correlated subquery solution.

Bill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources