How to grab the top items in a query (more complicated than you think)

Database

How to grab the top items in a query (more complicated than you think)

Hey all,

My coworker and I have a question.

 

Essentially, we have a Division (column A) and a Class (Column B). Division is the parent (20 total divisions) and Class is the child (anywhere from 1-10 Classes under each Division).

 

How can we pull say the top 5 Divisions as well as the top 2 Classes in each Division? Is it possible to do this in the same query? Currently, we are just pulling a giant data set, putting it in excel, creating a Pivot Table and then organizing our report off that pivot. We were hoping there was a way to put it all into one query so no pivot table is needed.

 

Thanks and hoping for an answer to our question!

 

2 REPLIES
N/A

Re: How to grab the top items in a query (more complicated than you think)

How do you define top 5 Divisions as well as the top 2 Classes?

 

Sounds like a task for an OLAP function.

Teradata Employee

Re: How to grab the top items in a query (more complicated than you think)

Yes, if there is a table or view org containing division, klass and revenue, and you are ranking these things by revenue, then something like this seems accurate:

 

select division
    ,"class"
    ,Drev
    ,Crev
from (
    select division
    ,"class"
    ,sum(revenue) over( partition by division) as Drev
    ,sum(revenue) over( partition by division, "class" ) as Crev
    from org
) S
qualify rank() over( order by Drev ) >= 5
    and rank() over( partition by division order by Crev ) >= 2
;