locked
Rewriting the ranking function RRS feed

  • Question

  • Hi all.

    I have the query below, I am trying to rewrite it such that the value for the first row col3 will be v8 and the value for col3 for row 5 will be v9.

    create table table1(
    K1 int,
    V1 nchar(10),
    V2 nchar(10),
    FFK1 int
    )
    
    
    create table table2(
    K2 int,
    V3 nchar(10),
    K3 int
    )
    
    
    insert into table1 select 1,'table2-1','V2-1',1
    insert into table1 select 1,'table2-4','table2-5',1
    insert into table1 select 1,'table2-6','table2-7',2
    insert into table1 select 1,'table2-8','table2-9',2
    
    
    insert into table2 select 1,'v5',1
    insert into table2 select 2,'v7',2
    insert into table2 select 1,'v8',3
    insert into table2 select 2,'v9',4
    
    
    
    select t1.V1, t1.V2,
    CASE ROW_NUMBER() OVER (PARTITION BY t2.k2 order by t2.k2)
    when 1 then t2.v3
    else null end col3
    from table1 t1 left join table2 t2 on T1.FFK1 = T2.k2
    
    
    
    

    Tuesday, May 26, 2020 6:36 PM

Answers

  • select t1.V1, t1.V2,
    CASE ROW_NUMBER() OVER (PARTITION BY t2.k2 order by t2.k3 DESC)
    when 1 then t2.v3
    else null end col3
    from table1 t1 left join table2 t2 on T1.FFK1 = T2.k2


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by MrFlinstone Tuesday, May 26, 2020 8:58 PM
    Tuesday, May 26, 2020 8:08 PM

All replies

  • select t1.V1, t1.V2,
    CASE ROW_NUMBER() OVER (PARTITION BY t2.k2 order by t2.k3 DESC)
    when 1 then t2.v3
    else null end col3
    from table1 t1 left join table2 t2 on T1.FFK1 = T2.k2


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by MrFlinstone Tuesday, May 26, 2020 8:58 PM
    Tuesday, May 26, 2020 8:08 PM
  • select t1.V1, t1.V2, t2.v3,
    CASE ROW_NUMBER() OVER (PARTITION BY t2.k2 order by t2.k3 Desc)
    when 1 then t2.v3
    else null end col3
    from table1 t1 left join table2 t2 on T1.FFK1 = T2.k2
    order by T2.k2, T2.V3 DESC
    Tuesday, May 26, 2020 9:04 PM