SQL server: calculate sequence in a SQL query

Question

I have the following four tables in SQL Server 2008R2:

DECLARE @ParentGroup TABLE (ParentGroup_ID INT, ParentGroup_Name VARCHAR(100));
DECLARE @ChildGroup TABLE (ChildGroup_id INT, ChildGroup_name VARCHAR(100), ParentGroup_id INT);
DECLARE @Entity TABLE ([Entity_id] INT, [Entity_name] VARCHAR(100));
DECLARE @ChildGroupEntity TABLE (ChildGroupEntity_id INT, ChildGroup_id INT, [Entity_ID] INT);
INSERT INTO @parentGroup VALUES (1, 'England'), (2, 'USA');
INSERT INTO @ChildGroup VALUES (10, 'Sussex', 1), (11, 'Essex', 1), (12, 'Middlesex', 1);
INSERT INTO @entity VALUES (100, 'Entity0'),(101, 'Entity1'),(102, 'Entity2'),(103, 'Entity3'),(104, 'Entity4'),(105, 'Entity5'),(106, 'Entity6');
INSERT INTO @ChildGroupEntity VALUES (1000, 10, 100), (1001, 10, 101), (1002, 10, 102), (1003, 11, 103), (1004, 11, 104), (1005, 12, 100), (1006, 12, 105), (1007, 12, 106);
/*
SELECT * FROM @parentGroup
SELECT * FROM @ChildGroup
SELECT * FROm @entity
SELECT * FROM @ChildGroupEntity
*/

The relationships between the tables as below:

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name],  0 [ChildGroupSequence], 0 [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name]

The output of the above query is:

-------------------------------------------------------------------------------
ParentGroup_Name|ChildGroup_name|Entity_name|ChildGroupSequence|EntitySequence|
-------------------------------------------------------------------------------
England         |Essex          |Entity3    |0                 |0             |
England         |Essex          |Entity4    |0                 |0             |
England         |Middlesex      |Entity0    |0                 |0             |
England         |Middlesex      |Entity5    |0                 |0             |
England         |Middlesex      |Entity6    |0                 |0             |
England         |Sussex         |Entity0    |0                 |0             |
England         |Sussex         |Entity1    |0                 |0             |
England         |Sussex         |Entity2    |0                 |0             |
-------------------------------------------------------------------------------

Now, I want to find out the child groups and all entities associated with the child groups for parent group 1. Also, I want to calculate the [ChildGroupSequence], [EntitySequence] as for the logic below:

1. The ChildGroupSequence column should represent the child group’s sequence within the parent group, starting from 1000 and incrementing by 100. Ie first subgroup will be 1000, second subgroup will be 1100.
2. The EntitySequence column should represent the entity sequence within the child group, starting from 100 and incrementing by single digits, resetting for each subgroup. I.e. the first entity in childgroup 1 starts at 100, as does the first entity in childgroup 2.

So, the output should be in the following format:

-------------------------------------------------------------------------------
ParentGroup_Name|ChildGroup_name|Entity_name|ChildGroupSequence|EntitySequence|
-------------------------------------------------------------------------------
England         |Essex          |Entity3    |1000              |100           |
England         |Essex          |Entity4    |1000              |101           |
England         |Middlesex      |Entity0    |1100              |100           |
England         |Middlesex      |Entity5    |1100              |101           |
England         |Middlesex      |Entity6    |1100              |102           |
England         |Sussex         |Entity0    |1200              |100           |
England         |Sussex         |Entity1    |1200              |101           |
England         |Sussex         |Entity2    |1200              |102           |
-------------------------------------------------------------------------------

I can do this easily by reading values into the application layer (.Net program), but want to learn SQL server by experimenting a few little things like this. Could anyone help me in writing this SQL query?

Any help would be much appreciated. Thanks in advance.

EDIT: My sample data hadn't seem to correctly reflect the first rule, the rule states that ChildGroupSequence should be incremented by 100 and the sample output increments by 1. The second query reflects the increment by 100. @jpw: Thank you very much for pointing this out.

Answer 1

I believe this can be accomplished using partitioning and ranking functions like this:

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 
    999 + DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) AS [ChildGroupSequence], 
    99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY ChildGroup_name, Entity_name) AS [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name]

This query generates the sample output you described. Your sample data does not seem to correctly reflect the first rule though as the rule states that ChildGroupSequence should be incremented by 100 and the sample output increments by 1. The second query reflects the increment by 100:

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 
    900 + 100 * DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) AS [ChildGroupSequence], 
    99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY ChildGroup_name, Entity_name) AS [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name]

Please see this sample SQL Fiddle for examples of both queries.

Maybe the query should partition by ID and not Name, if so Sussex will come before Essex as it has a lower ID and the query would be this:

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 
    900 + 100 * DENSE_RANK() OVER(PARTITION BY pg.ParentGroup_ID ORDER BY cg.ChildGroup_ID) AS [ChildGroupSequence], 
    99 + ROW_NUMBER() OVER(PARTITION BY pg.ParentGroup_ID, cg.ChildGroup_ID ORDER BY cg.ChildGroup_ID, cge.Entity_ID) AS [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY pg.ParentGroup_ID, cg.ChildGroup_ID, [Entity_name]

Answer 2

SELECT  ParentGroup_Name, 
        ChildGroup_name,
        [Entity_name],  
        LU.R [ChildGroupSequence], 
        99 + ROW_NUMBER() OVER (PARTITION BY LU.ParentGroup_id,LU.ChildGroup_id ORDER BY ChildGroup_name) [EntitySequence]
FROM    @ChildGroupEntity cge
JOIN    (
        SELECT  cg.ChildGroup_id,   
                cg.ChildGroup_name, 
                pg.ParentGroup_id,  
                pg.ParentGroup_Name,    
                999 + (ROW_NUMBER() OVER (ORDER BY cg.ChildGroup_id)) [R]
        FROM    @ChildGroup cg 
        JOIN    @parentGroup pg On pg.ParentGroup_ID = cg.ParentGroup_ID) LU
        ON      cge.ChildGroup_id = LU.ChildGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY LU.ParentGroup_Name, LU.ChildGroup_name, e.[Entity_name]

Result:

Answer 3

You can solve this by using ranking functions

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name],
       899 + DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) +
       100 * DENSE_RANK() OVER(ORDER BY ParentGroup_Name ASC) AS ChildGroupSequence,
       99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY [Entity_name]) AS [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name]

Result you can find here SQL Fiddle