none
Get top 5 column values RRS feed

  • Question

  • Hi ,

    I have a table in this format  MemberName|Title1|Title2|Title3|Title4|Title5|Title6|Title7|Title8

    Each title column has a score in it. 

    I need a query which gets MemberName and its top 5 title score.

    Any help is appreciated.

    Monday, June 6, 2016 4:01 PM

Answers

  • Your table is not structured well to get what you need. I would unpivot the results and get the top 6 that way.

    with bse as (select 
    MemberName, Title, Val
    FROM (SELECT 
    MemberName,Title1,Title2,Title3,Title4,Title5
    ,Title6,Title7,Title8
    from tbl
    ) as t
    unpivot (
    Val for Title in (
    Title1,Title2,Title3,Title4,Title5
    ,Title6,Title7,Title8
      )
    
    
    ) as unp
    )
    , rnk as (
    select *, row_number() over (
    partition by MemberName
    order by val desc) as RowNum
    from bse
    
    )
    select MemberName, Sum(Val) as Top5TitleValue
    from rnk
    where RowNum <= 5
    group by MemberName


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by nextwarren Monday, June 6, 2016 7:14 PM
    Monday, June 6, 2016 4:09 PM