0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Waren Schild
  • 13
  • 3

1 Answers1

0

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.

pkd
  • 471
  • 1
  • 6
  • 17
  • 1
    if you use `row_number()` it will also give the same result – GuidoG Apr 13 '22 at 14:10
  • Yes, it will give same result. – pkd Apr 13 '22 at 14:20
  • 1
    No `ROW_NUMBER` is more correct, as `RANK` will give different results if there are ties – Charlieface Apr 13 '22 at 14:53
  • Yes, but as per my understanding of data, if we have ties based on the floor then it makes more same to assign them the same counter if they belong to the same building and floor. Based, on this assumption I used RANK(), but yes, if this assumption is wrong then we can use row_number(). I have edited my answer – pkd Apr 13 '22 at 15:06