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;