SQL Server: Row ordering by group and sequence

Question

I am stymied on something that seems like it should be really simple but I am too tired to mess with it anymore right now so I am just throwing this out here to see if anyone can show how to go about this. What I am attempting to do is identify the rows of a table according to the group, the sequence, and the order of the sequence to which the values in the table belong as ordered by the IDENTITY column (ID). I am using Microsoft SQL Server 2008 R2 Management Studio (v10.50.4000.0).

declare @X table (
ID int identity,
Value varchar(20)
);

insert @X
select 'abc'
union all
select 'zzz' --def
union all
select 'abc'
union all
select 'abc'
union all
select 'xyz'
union all
select 'abc'
union all
select 'abc';

select * from @X;

The end result should look something like this:

/*
*GO-GroupOrder; SO-SequenceOrder; GSO-GroupSequenceOrder
ID  Value   GO  SO  GSO
1   abc     1   1   1
2   zzz     2   2   1 --def
3   abc     1   3   2
4   abc     1   3   2
5   xyz     3   4   1
6   abc     1   5   3
7   abc     1   5   3
*/

I hope that it is acceptable that I am going to spare you the variety of failed attempts (involving row_number, rank, dense_rank, group by, etc) which I have made thus far ...; I am sure there must be a relatively simple solution that doesn't involve much more than a single operation across the set but I can't figure it out. NOTE: Edited Value def to zzz to make the requested ordering more clear. I hope that makes sense and thanks in advance!

SOLUTION:

with
    cte1 as (
        select
            x.ID,
            x.Value,
            oX.ValuePrevious
        from @X as x
        outer apply (
            select
                top 1
                    oX.Value as ValuePrevious
            from @X as oX
            where x.ID > oX.ID
            order by oX.ID desc
            ) as oX
        ),
    cte2 as (
        select
            min(ID) as IDMin,
            Value
        from @x
        group by Value
        ),
    cte3 as (
        select
            cte1.ID,
            cte1.Value,
            dense_rank() over (order by cte2.IDMin) as [GO],
            cCTE1.SO
        from cte1
        cross apply (
            select
                sum(case 
                    when 1 <> 1
                        or cCTE1.ValuePrevious != cCTE1.[Value]
                        or cCTE1.ValuePrevious is NULL
                    then 1
                    else 0
                    end) as SO
            from cte1 as cCTE1
            where cte1.ID >= cCTE1.ID
            ) as cCTE1
        join cte2
            on cte2.Value = cte1.Value
        )
    select
        ID,
        Value,
        [GO],
        SO,
        dense_rank() over (partition by [GO] order by SO) as [GSO]
    from cte3 order by ID;

TriV · Accepted Answer · 2017-08-01T15:20:04.187

1

If i understand correctly, your query would be

 ;WITH temp AS
(
    select  x.ID,
            x.[Value], 
            pre.PreviousValue      
    from @X x
    OUTER APPLY
    (
        SELECT  TOP 1 
                x2.[Value] AS PreviousValue
        FROM @X x2
        WHERE x2.ID < x.ID
        ORDER BY x2.ID DESC
    ) pre
),
temp1 AS
(
    SELECT x2.[Value], min(x2.ID) AS MinGrId
    FROM @X x2
    GROUP BY x2.[Value]
),
temp2 AS
(
select  t.*,
       SUM(CASE 
             WHEN t.PreviousValue != t.[Value] OR t.PreviousValue IS null THEN 1 
             ELSE 0
          END) OVER(ORDER BY t.ID) AS [SO],
        dense_rank() OVER(ORDER BY t1.MinGrId) AS [GO]
from temp t    
INNER JOIN temp1 t1 ON t.[Value] = t1.[Value]
)
SELECT t.ID, t.[Value], t.[GO], t.SO,
      dense_rank() OVER(PARTITION BY t.[GO] ORDER BY t.SO) AS GSO
FROM temp2 t
ORDER BY t.ID

Demo link: http://rextester.com/KGYT17255

edited Aug 01 '17 at 15:20

answered Aug 01 '17 at 04:35

TriV

5,118
2
10
18

I forgot to mention that I am using Microsoft SQL Server 2008 R2 Management Studio (v10.50.4000.0) so function LAG is not available to me. However, a quick search led me to this (https://stackoverflow.com/questions/33283205/lag-lead-equivalent-with-grouping-sql-server-2008-r2) which might help me do the equivalent for that piece. Also, the the orderings need to be in order of ID (the identity column) and not Value. Thanks. – Erg Aug 01 '17 at 13:17
In sql server 2008, you can use `APPLY` instead of `LAG` and order items by minimum id of each group. I edited my answer. – TriV Aug 01 '17 at 15:23
Unfortunately, I am working with ancient technology (2008 R2), so ORDER BY cannot be used with aggregate window functions (https://stackoverflow.com/questions/12686140/issue-with-window-function-in-mssql-2008-r2). But maybe this will help me fix that piece (https://stackoverflow.com/questions/38216962/sql-server-2008-r2-running-sum?rq=1). Thanks again! – Erg Aug 01 '17 at 16:09

SQL Server: Row ordering by group and sequence

1 Answers1