Top 10 for each group

knows_not_much

 I have a SQL query where I am doing a group by. I want to have the top 10 for each group.

 

I searched the internet but all solutions tend to be specific to a particular database (like oracle, or sql )

 

Is there a solution for Vertica?

DerekW

The group definition in your group by and your top determination should be different, otherwise it won't make any sense. The group by will result in a unique row, so doing a top 10 on the same group will result in the same unique row.

 

So either you determine the top 10 before you do the group by, or you determine the top 10 after the group by on a different group. Determining the top 10 can be achieved in multiple ways, but the easiest in my experience is using the LIMIT clause.

 

select col1, col2, value

from table

limit 10 over (partition by col1, col2 order by value desc) -- top 10 assuming high value is better

 

-- determining top 10 after group by

select col1, col2, sum(value) as total_value

from table

group by col1, col2

limit 10 over (partition by col1 order by sum(value) desc)

 

-- group by after determining top 10 (using subquery)

select col1, col2, sum(value) as total_value

from (select col1, col2, value from table limit 10 over (partition by col1, col2 order by value desc)) a

group by col1, col2