Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Top 10 for each group

 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?

Comments

  • 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

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.