Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL server: calculate sequence in a SQL query

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.

like image 947
Sathish Avatar asked Sep 03 '14 12:09

Sathish


People also ask

How do I query sequence in SQL?

The syntax to a view the properties of a sequence in SQL Server (Transact-SQL) is: SELECT * FROM sys. sequences WHERE name = 'sequence_name'; sequence_name.

How do I add a sequence number to a select query?

The Rank function can be used to generate a sequential number for each row or to give a rank based on specific criteria. The ranking function returns a ranking value for each row. However, based on criteria more than one row can get the same rank.

How do I find the next value of a sequence in SQL Server?

SELECT - For each referenced sequence object, a new value is generated once per row in the result of the statement. INSERT ... VALUES - For each referenced sequence object, a new value is generated once for each inserted row in the statement.


3 Answers

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]
like image 146
jpw Avatar answered Sep 23 '22 04:09

jpw


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:

enter image description here

like image 21
Jithin Shaji Avatar answered Sep 22 '22 04:09

Jithin Shaji


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

like image 32
Aleksandr Fedorenko Avatar answered Sep 23 '22 04:09

Aleksandr Fedorenko