Match features when multiple attributies are the same

Question

Getting stuck with a SQL statement that i'm using for the InlineQuerier transformer for FME. I normally do not work that much with SQL code and only use if to perform simple one on one joins. I am working with one data frame at the moment that contains multiple attributes and i want to create an extra attribute based on these. An short example:

ID  road_name           road_type    Type     Traffic
1   bakerstreet         elements     B        light
2   bakerstreet         elements     B        light
3   piccadilly circus   asphalt      A        heavy
4   woodstreet          concrete     A        heavy
5   settlerstreet       concrete     A        heavy
6   woodstreet          concrete     A        heavy
7   settlerstreet       concrete     A        heavy
8   settlerstreet       concrete     B        heavy

I am trying to give all the features a unique RoadID based on all 4 attributes so i can merge them later on. The new attribute RoadID must be a data type Integer and the value should be matching a unique combination of the 4 attributes.

The results should be something like:

ID  road_name           road_type    Type     Traffic   RoadID
1   bakerstreet         elements     B        light     1
2   bakerstreet         elements     B        light     1
3   piccadilly circus   asphalt      A        heavy     2
4   woodstreet          concrete     A        heavy     3
5   settlerstreet       concrete     A        heavy     4
6   woodstreet          concrete     A        heavy     3 
7   settlerstreet       concrete     A        heavy     4
8   settlerstreet       concrete     B        heavy     5

Do i use concat for it or should a simple if then statement do the trick?

Answer 1

Check This.

using DENSE_RANK() :

        with CTE as
        (
        select distinct * ,
        max(id) over ( Partition by road_name,road_type, Type ,Traffic )  mn    
        from #TableName
        )
        select ID,road_name,road_type,Type,Traffic,dense_rank() over ( order by mn ) RowID 
        from CTE C 
        order by id

OutPut :

Answer 2

Try min and dense_rank window functions:

Try this:

select
    Id, road_name, road_type, Type, Traffic,
    dense_rank() over (order by min_id) roadId
from (
    select
        t.*,
        min(id) over (partition by road_name, road_type, Type, Traffic) min_id
    from your_table t
) t order by id

Live demo

Answer 3

with src(ID,road_name,           road_type,    Type,     Traffic) as (
    select 1,'bakerstreet','elements','B','light' from dual union all
    select 2,'bakerstreet','elements','B','light' from dual union all
    select 3,'piccadilly circus','asphalt','A','heavy' from dual union all
    select 4,'woodstreet','concrete','A','heavy' from dual union all
    select 5,'settlerstreet','concrete','A','heavy' from dual union all
    select 6,'woodstreet','concrete','A','heavy' from dual union all
    select 7,'settlerstreet','concrete','A','light' from dual union all
    select 8,'settlerstreet','concrete','B','heavy' from dual )
select a.* 
 ,dense_rank() over( order by road_name, road_type, Type, Traffic )
 from src a;