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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 generating a grouped counter field
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

billsox
Yak Posting Veteran

74 Posts

Posted - 12/05/2002 :  12:43:27  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 12/05/2002 :  14:06:25  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 12/05/2002 :  14:08:45  Show Profile  Reply with Quote
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



Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/05/2002 :  14:09:45  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

billsox
Yak Posting Veteran

74 Posts

Posted - 12/05/2002 :  18:39:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000