SQL comparison of adjacent rows and sorting of decreasing sequences inside the table

28 views Asked by At

I work in SSMS 18, tsql queries

Task: Given a positive integer. It is necessary to place all descending sequences of adjacent digits in ascending order. Output the result to the system messages area.

It is necessary to perform the task using a tabular method, that is, write a number to the table. Valid code for writing a number to a table:

declare @table table (digit int, num int)
declare @x1 int = 210030331 --12003313
declare @i int = 1
while @x1 > 0
begin
    insert into @table values (@x1%10, @i)
    set @i+=1
    set @x1/=10
end

When writing a number to a table, it is only allowed to write the digits of the number to the table and specify the id for each row. After that, just use select to collect a new number and output it. How sequences should unfold: there is a number 210030331 it has decreasing sequences 210 0 30 3 31. After we turn them over (replace them with an increasing sequence), the number 12003313 should be obtained.

What have you tried: Comparing numbers using the LAG, LEAD window functions to compare previous and subsequent values. Tried to embed code using ROW_NUMBER and RANK. Comparison using join with yourself. I do not apply the code with attempts, so as not to confuse.

It does not work: adequately compare the numbers in the sequence and give numbers to groups of sequences (as indicated below)

The essence of the solution is something like this:

Identify decreasing sequences by comparing strings by id.

Numbering of each sequence (we give the number to the group): 1 1 3 1 3 2 0 3 3 3 0 4 0 5 1 5 2 5

Then sort by the group number in descending order (desc), and by the digits of the number in ascending order (asc).

Please tell me what to do

0

There are 0 answers