CODEWITHSUNDEEP
sqloracle-database
RazorSky
RazorSky

Reputation: 360

Top N results grouped Oracle SQL

I want to write a query that allows me to only get the specific data I want and nothing more.

We will use TV's as an example. I have three brands of TVs and I want to see the top ten selling models of each brand. I only want to return 30 rows. One solution is unions, but that can get messy fast. Ideally there would be a WHERE ROWNUM grouping by situation.

SELECT 
    A.Brand
,   A.Model
,   A.Sales
FROM 
(   SELECT
        TV.Brand
    ,   TV.Model 
    ,   SUM(TV.SALES) AS SALES
    FROM TV_TABLE as TV
    ORDER BY 
        TV.Brand
    ,   SALES DESC
)   A
WHERE ROWNUM <10

In my code above I will get the top 10 total results from the inner query, but not 10 from each Grouping.

What I want to see is something like this:

Brand: Model: Sales
Sony: x10: 20
Sony: X20: 18
Sony: X30: 10
VISIO: A40: 40
VISIO: A20: 10

This is an oversimplified example, in practice I'll need to have 20-50 gropings and would like to avoid downloading all of the data and using a Pivot feature.

Upvotes: 2

Views: 552

Answers (2)

Chamly Idunil
Chamly Idunil

Reputation: 1872

SELECT TV.Brand, TV.Model, SUM(TV.SALES) AS SALES
FROM TV_TABLE TV
group by TV.Brand, TV.Model
order by SUM(TV.SALES) desc, TV.Brand
limit 30

Upvotes: 0

David Weinberg
David Weinberg

Reputation: 1091

 select Brand, Model, SALES
 from(
    select Brand, Model, SALES,row_number()over(partition by Brand order by SALES desc) rn
    from (
        SELECT TV.Brand, TV.Model,SUM(TV.SALES) AS SALES,
        FROM TV_TABLE as TV
        group BY TV.Brand,TV.Model
    )a
)b
where rn <= 10

Upvotes: 5

Related Questions