Top 10 from each Group By

scorpioviper

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;

scorpioviper

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