TSQL retreive the next item if item has already been assigned

user_5853573 0
Viewed 35

I have a sample data set which looks like:

enter image description here

I need to return the next value if the value hasn't already been taken.

I would like to achieve this by not using a cursor. The data which should be returned is highlighted in yellow.

Any ideas?

Thanks

recursive-query greatest-n-per-group tsql sql
1 Answers

Here is one approach using a recursive cte and window functions:

with 
    tab as (
        select 
            t.*, 
            dense_rank() over(order by item) drn
        from mytable t
    ),
    cte as (
        select t.* from tab t where drn = 1
        union all
        select t.*
        from tab t
        inner join cte c on t.drn = c.drn + 1 and t.itemb > c.itemb
    )
select select item, itemb, date
from cte c
where itemb = (select min(itemb) from cte c1 where c1.item = c.item)

Derived table tab assigns ranks to each record according to item (records having the same item get the same rank).

Then, the recursive cte starts from the rows corresponding to the first item, and processes items one by one, ensuring that the "next" itemb is greater than the preceding.

Finally, the outer query filters on the first itemb per item.

Demo on DB Fiddle:

item | itemb | date      
:--- | :---- | :---------
X    | A     | 2014-01-01
Y    | B     | 2014-01-02
Z    | C     | 2014-01-02
user_106767160
Related