Retrieving top company for each quarter and corresponding revenue-CodePudding

Company_name	Quarter	Year	Revenue
TCS	Q1	2001	50
CTS	Q2	2010	60
ZOHO	Q2	2007	70
CTS	Q4	2015	90

This is my sample table where I store the names of the companies, quarters of the years, years and revenue for each year per a certain quarter.

I want to find the company with top revenue for each quarter, regardless of the year, and display its revenue too.

In the above case the resultant output should be something like this:

QUARTER	COMPANY_NAME	REVENUE
Q1	TCS	50
Q2	ZOHO	70
Q4	CTS	90

Here's what I've tried:

SELECT DISTINCT(C1.QUARTER), 
       C1.REVENUE 
FROM COMPANY_REVENUE C1, 
     COMPANY_REVENUE C2 
WHERE C1.REVENUE = GREATES(C1.REVENUE, C2.REVENUE);

Thanks in advance for any help.

CodePudding user response：

Try this,

SELECT quarter, company_name,max(revenue) FROM table_name GROUP BY quarter

CodePudding user response：

A cte would work:

with x as (
  select Company_name, max(Revenue) as Revenue 
  from table
  group by Company_name
)
select x.Company_name, t.Quarter, x.Revenue
from x
join table t
on x.Revenue = t.Revenue;

see db<>fiddle

CodePudding user response：

There are a couple of problems in your query, among which:

the fact that the DISTINCT keyword can be applied to full rows rather than single fields,
the SELF JOIN should be explicit, though most importantly it requires a matching condition, defined by an ON clause (e.g. SELECT ... FROM tab1 JOIN tab2 ON tab1.field = tab2.field WHERE ...)

Though probably you could solve your problem in another way.

One way of computing values on partitions (in your case you want to partition on quarters only) is using window functions. In the specific case you can use ROW_NUMBER, which will compute a ranking over your revenues descendently for each selected partition. As long as you want the highest revenue for each quarter, you can select the row number equal to 1 for each quarter group.

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER(
               PARTITION BY Quarter 
               ORDER     BY Revenue DESC
           ) AS rn
    FROM tab
)
SELECT Quarter,
       Company_name,
       Revenue
FROM cte 
WHERE rn = 1

Check the demo here.