Retrieving top company for each quarter and corresponding revenue

Question

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 = GREATEST(C1.REVENUE, C2.REVENUE);

Can you share your best coding attempt at this problem with us, and why it doesn't work? — lemon, Jul 03 '22 at 16:36
Duplicate of [Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) — philipxy, Jul 04 '22 at 02:20

Shmiel · Answer 1 · 2022-07-04T13:34:00.563

You can just use a cte:

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

see db<>fiddle.

First you select the max Revenue group by Quarter, then I'm joining to the table on the returned max(Revenue) but as @lemon pointed out in comments that's not enough because what would happen when there's two revenues on same company but different quarters it will return more rows as shown in this db<>fiddle.

So that's why I need to add the join on quarter so it will only return one result per quarter.

But if you're using a version of MySql that doesn't support cte you can use a subquery like:

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

lemon · Answer 2 · 2022-07-03T17:25:40.580

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.

Approach for MySQL 8.0

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.

Approach for MySQL 5.7

In this case you can use an aggregation function. As long as you want your max "Revenue" for each "Quarter", you need first to select the maximum value for each "Quarter", then you need to join back to your original table on two conditions:

table's quarter matches subquery quarter,
table's revenue matches subquery max revenue

SELECT tab.Quarter,
       tab.Company_name, 
       tab.Revenue
FROM       tab 
INNER JOIN (SELECT Quarter, 
                   MAX(Revenue) AS Revenue 
            FROM tab
            GROUP BY Quarter               ) max_revenues
        ON tab.Quarter = max_revenues.Quarter
       AND tab.Revenue = max_revenues.Revenue

Check the demo here.

Note: the second solution will find for each quarter all companies that have the maximum revenue for that quarter, which means that if two or more companies have the same maximum value, both will be returned. This won't happen for the first solution, as long as the ranking ensures only one (the ranked = 1) will be retrieved.

So If I follow your second solution what'd I need to do if I wanted the second and third max instead of company with top revenue — Stack_coder, Jul 04 '22 at 01:42
If you attempt to retrieve second and third max with aggregation functions, it becomes quite complex, I wouldn't venture that path tbh. With window functions it is a snap of fingers (just change `WHERE rn = 1` to `WHERE rn IN (2, 3)`) — lemon, Jul 04 '22 at 02:22
Consider marking your question as answered to save other devs time on yet unsolved problems. @Stack_coder — lemon, Jul 04 '22 at 10:02

score -1 · Answer 3 · answered Jul 03 '22 at 16:36

-1

Try this,

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

answered Jul 03 '22 at 16:36

Aravind Rooswelt

1

1

It does throw an error, see [DBFIDDLE](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=61dfe57a2e479aa9f9a1446e0681f446). For explanation read: [12.20.3 MySQL Handling of GROUP BY](https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html) – Luuk Jul 03 '22 at 17:06
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 04 '22 at 05:19

Retrieving top company for each quarter and corresponding revenue

3 Answers3