SQL Query to define "Best Artists" [?]

Question

I am still learning Oracle SQL and would like your guidance.

Let's say we have a table MONTHLY_SALES_TOTALS, which has 3 fields: name , region , amount . We need to identify the best sellers in each region. The best means that their number is equal to the maximum for the region.

 CREATE TABLE montly_sales_totals ( name varchar(20), amount numeric(9), region varchar(30) ); INSERT ALL INTO montly_sales_totals (name, amount, region) VALUES ('Peter', 55555, 'east') INTO montly_sales_totals (name, amount, region) VALUES ('Susan', 55555, 'east') INTO montly_sales_totals (name, amount, region) VALUES ('Mark', 1000000, 'south') INTO montly_sales_totals (name, amount, region) VALUES ('Glenn', 50000, 'east') INTO montly_sales_totals (name, amount, region) VALUES ('Paul', 500000, 'south') SELECT * from dual; 

Possible Solution:

 SELECT m1.name, m1.region, m1.amount FROM montly_sales_totals m1 JOIN (SELECT MAX(amount) max_amount, region FROM montly_sales_totals GROUP BY region) m2 ON (m1.region = m2.region) WHERE m1.amount = m2.max_amount ORDER by 2,1; 

SQL Fiddle: http://sqlfiddle.com/#!4/6a2d8/6

Now my questions are:

• How effective is such a request?
• How can / should be simplified and / or improved?
• I could not use Top , since the number of lines "max" depends on the region. Is this another direct functionality that I could use instead?

Answer 1

I would use RANK ():

 SELECT * FROM ( SELECT name, amount, region, RANK() OVER (PARTITION BY region ORDER BY amount DESC) rnk FROM montly_sales_totals ) t WHERE t.rnk = 1 

Here is a modified version of SQL Fiddle

Answer 2

There are several ways to do this. Here is another:

  select S.region, S.name, V.regionmax from sales as S inner join ( select region, max(amount) as regionmax from sales group by region ) as V on S.region = V.region and S.amount = regionmax 

In terms of efficiency, the main factor is the use of the right index (s). Inline views can work very well.

Answer 3

I like the CTE syntax, but using this website, the time taken is 2 ms, so I can't win :)

 with Maximums as ( SELECT region, MAX(amount) max_amount FROM montly_sales_totals GROUP BY region ) SELECT m1.name, m1.region, m1.amount FROM montly_sales_totals m1, Maximums WHERE (m1.amount = Maximums.max_amount) and (m1.region = Maximums.region) ORDER by 2,1; 

Answer 4

you can do this using a function too ...

 select * from (select m1.*, row_number( ) over (partition by m1.region order by m1.amount desc,m1.name desc ) max_sal from montly_sales_totals m1 ) where max_sal =1 ; 

this query can do one more thing if both salvars are the same!