sql study for row_number()

test.sql

[SQL Fiddle][1]

**MS SQL Server 2017 Schema Setup**:

    create table t1(
       id int not null primary key
      ,name varchar(100) not null
      ,dept varchar(100) null
      ,age int
    );
    insert into t1 values(1,'Alice','Dev',23);
    insert into t1 values(2,'Bob','Dev',25);
    insert into t1 values(3,'Cachy','Ops',29);
    insert into t1 values(4,'Dixi','Dev',34);
    insert into t1 values(5,'Effy','Sales',35);
    insert into t1 values(7,'George','Dev',36);
    insert into t1 values(8,'George','Ops',36);
    insert into t1 values(9,'Hydyn','Sales',28);
    insert into t1 values(10,'Jack','HR',38);
    
    create table t2(
      dept varchar(100) not null
      ,location varchar(100) not null
    );
    insert into t2 values('Dev','Tokyo');
    insert into t2 values('Ops','Chiba');
    insert into t2 values('Sales','Osaka');
**Query 1**:

    select 
     row_number() over(order by name) seq
     ,src.*
     ,t2.location
    from
    (select
      name
      ,dept
      ,row_number() over(partition by dept order by age) as dept_seq
    from t1
     ) as src
     left outer join t2
     on src.dept = t2.dept
    

**[Results][2]**:
    | seq |   name |  dept | dept_seq | location |
    |-----|--------|-------|----------|----------|
    |   1 |  Alice |   Dev |        1 |    Tokyo |
    |   2 |    Bob |   Dev |        2 |    Tokyo |
    |   3 |  Cachy |   Ops |        1 |    Chiba |
    |   4 |   Dixi |   Dev |        3 |    Tokyo |
    |   5 |   Effy | Sales |        2 |    Osaka |
    |   6 | George |   Ops |        2 |    Chiba |
    |   7 | George |   Dev |        4 |    Tokyo |
    |   8 |  Hydyn | Sales |        1 |    Osaka |
    |   9 |   Jack |    HR |        1 |   (null) |

  [1]: http://sqlfiddle.com/#!18/508e0/6
  [2]: http://sqlfiddle.com/#!18/508e0/6/0