CODEWITHSUNDEEP
sqlcount
Phillip Senn
Phillip Senn

Reputation: 47605

SQL Select Counter by Group

Here is the code I've written to create a scenario:

USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Emp') AND type in (N'U'))
DROP TABLE Emp
GO
CREATE TABLE Emp(
EmpID Int Identity(10,1) Primary Key,
EmpGroupID Int)
GO
INSERT INTO Emp(EmpGroupID) VALUES(1000)
INSERT INTO Emp(EmpGroupID) VALUES(1000)
INSERT INTO Emp(EmpGroupID) VALUES(1000)
INSERT INTO Emp(EmpGroupID) VALUES(2000)
INSERT INTO Emp(EmpGroupID) VALUES(2000)
INSERT INTO Emp(EmpGroupID) VALUES(2000)
INSERT INTO Emp(EmpGroupID) VALUES(3000)
GO
SELECT * FROM Emp
ORDER BY EmpGroupID,EmpID

What I need is for each group to have a counter variable, incrementing by 1, such that all the rows for Group 1000 have counter=1, groupid=2000 has counter=2, groupid=3000 has counter=3. 

SELECT ?,EmpID,EmpGroupID 
FROM Emp
ORDER BY EmpGroupID,EmpID
-- The result I'm looking for is:
1,10,1000
1,11,1000
1,12,1000
2,13,2000
2,14,2000
2,15,2000
3,16,3000

Upvotes: 2

Views: 4766

Answers (6)

Rodrigo Lins
Rodrigo Lins

Reputation:

Try this:

SELECT DENSE_RANK() OVER (ORDER BY EmpID) as 'counter',GroupID 
FROM Emp
ORDER BY counter, EmpGroupID

Upvotes: 1

Amy B
Amy B

Reputation: 110111

You're describing a dense ranking of groups:

SELECT
  DENSE_RANK() OVER (ORDER BY EmpGroupID) as Counter,
  EmpID,
  EmpGroupID
FROM Emp
ORDER BY EmpGroupID,EmpID

And here's some reference material: http://msdn.microsoft.com/en-us/library/ms189798.aspx

Upvotes: 5

Joel Coehoorn
Joel Coehoorn

Reputation: 415810

It took me a while to understand what you were asking. As I understand it, you want to create and populate the 'Counter' column based on the EmpGroupID? If so, then something like this:

SELECT EmpGroupID, EmpID,
    (SELECT COUNT(*) +1 
     FROM [table] 
     WHERE t2.EmpGroupID < t1.EmpGroupID GROUP BY t2.EmpGroupID
    ) AS Counter
FROM [table] t1
ORDER BY EmpGroupID, EmpID

Upvotes: 1

Michael Petrotta
Michael Petrotta

Reputation: 60902

You mean, you need a query that produces textual output with the commas as shown?

Try:

SELECT Counter + ',' + EmpGroupID + ',' + EmpID
FROM Table
ORDER BY EmpGroupID

Upvotes: 4

dkretz
dkretz

Reputation: 37655

Guessing from your description, do you want something like

SELECT EmpGroupID, EmpID, COUNT(1) AS Counter
FROM some-table-name
GROUP BY EmpGroupID, EmpID
ORDER BY COUNT(1), EmpGroupID, EmpID

That's for SQL Server - in other cases you may be able to say

ORDER BY Counter, EmpGroupID, EmpID

Upvotes: 1

Malfist
Malfist

Reputation: 31795

ORDER BY can have more than one clause

Try 

SELECT Counter,EmpGroupID, EmpID
ORDER BY Counter,EmpGroupID, EmpID

Upvotes: 3

Related Questions