We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Top 10 from each Group By — Vertica Forum

Top 10 from each Group By

Hi,

 

Here's my simple table structure. Each account can have multiple office equipments (potentially 1000's).

 

I'm looking to write an SQL that'd group records by accounts but use the rank() or dense_rank() function to simply pick only the Top-2 from each account.

 

account_name | office_equipment

------------------------------------------------

A1                          | E1

A1                          | E2

A1                          | E3

A1                          | E4

------------------------------------------------

A2                          | B1

A2                          | B2

A2                          | B3

------------------------------------------------

A3                          | C1

A3                          | C2

------------------------------------------------

 

In other words, I'd like a result set as below.....

 

account_name | office_equipment | rank
---------------------------------------------------------------
A1                          | E1                              | 1
A1                          | E2                              | 1

---------------------------------------------------------------

A2                          | B1                              | 2
A2                          | B2                              | 2

---------------------------------------------------------------

A3                          | C1                              | 3
A3                          | C2                              | 3

 

I've tried below SQL, but it's not giving me the desired result (I'm getting the same rank across all accounts).

 

SELECT * FROM
(
 SELECT
        ACCOUNT_NAME,
        OFFICE_EQUIPMENT_NAME,
        RANK() OVER (PARTITION BY ACCOUNT_NAME ORDER BY ACCOUNT_NAME) AS RANK
 FROM
        ACCOUNT_EQUIPMENT
) AS OUTERSQL

WHERE RANK = 1;

Comments

  • I tried a few combinations & now below SQL is indeed giving me a unique rank for each account.

     

    But my other requirement is still not fulfilled, wherein i want to limit the # of rows for each account (ex: Top-N for each Account)

     

    SELECT * FROM
    (      SELECT
                    ACCOUNT_NAME,
                    OFFICE_EQUIPMENT_NAME,
                    DENSE_RANK() OVER (ORDER BY ACCOUNT_NAME) AS RANK
          FROM
                   ACCOUNT_EQUIPMENT
    ) AS OUTERSQL 

Leave a Comment

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