'SQL create a dynamic sequential number in a select statement based on a SortOrder

I want to generate a new column with a sequential number based on a data column SortOrder.

The Sortorder is returned from a subquery, where I manually mapped floor designations of buildings.

Building Floor SortOrder
BuildingA 1. floor 3
BuildingA rooftop 11
BuildingA ground 0
BuildingB Ground 0
BuildingB rooftop 11
BuildingB secondfloor 4

This is my current query. Now I want a new column that looks like that

Building Floor SortOrder Counter
BuildingA 1. floor 3 2
BuildingA rooftop 11 3
BuildingA ground 0 1
BuildingB Ground 0 1
BuildingB rooftop 11 3
BuildingB secondfloor 4 2

The query needs to group the buildings by their names, orders them by Sort order and create based on that a sequential number.

I tried messing around with ROW_NUMBER OVER by joining a subquery with a group by, but that did not work out.

sqlsql-serversortingsequential-number


Solution 1:[1]

You can use RANK() or DENSE_RANK() based on what you need, as I don't have clarity more about your data I"m going with RANK() in my solution.

with cte as (

select 'BuildingA' as Building, '1.floor'    as Floor ,3 as SortOrder
union all
select 'BuildingA' as Building, 'rooftop'    as Floor ,11 as SortOrder
union all
select 'BuildingA' as Building, 'ground'     as Floor ,0 as SortOrder
union all
select 'BuildingB' as Building, 'Ground'     as Floor ,0 as SortOrder
union all
select 'BuildingB' as Building, 'rooftop'    as Floor ,11 as SortOrder
union all
select 'BuildingB' as Building, 'secondfloor'    as Floor ,4 as SortOrder
)

select *, RANK() over(partition by building order by sortorder ) as Counter from cte

With this you get your desired output.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 pkd

Related Questions