Mysql: How can i join two tables and find the revenue of each company for latest year

Question

This is Two tables with data, how to join both these tables and find the revenue of each company for the latest available year. How can I write SQL query to get expected result?

  Table ‘Companies’:

    Id | companyCode| companyName
    1 | comp1      | Tata Motors
    2 | comp2      | Reliance
    3 | comp3      | Infosys
    4 | comp4      | HDFCBANK

 Table ‘FinancialDatas’

   Id | companyCode| year | revenue
   12 | comp1      | 2019 | 12500
   13 | comp1      | 2020 | 13250
   14 | comp2      | 2018 | 45000
   15 | comp2      | 2019 | 55000
   16 | comp3      | 2019 | 9500
   17 | comp3      | 2020 | 7500
   18 | comp4      | 2017 | 11000
   19 | comp4      | 2018 | 13500

Extexted OutPut Below:

  companyName | year | revenue
  Tata Motors |2020  |13250
  Reliance    |2019  |55000
  Infosys     |2020  |7500
  HDFCBANK    |2018  |13500

Please read https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions and publish what you have so far. — P.Salmon, Oct 27 '20 at 09:10

score 0 · Accepted Answer · answered Oct 27 '20 at 09:13

0

You can use row_number()

select * from
(
select companyName,year,revenue,row_number() over(partition by companyName order by year desc) as rn
from companies c join FinancialDatas f on c.companycode=f.companycode
)A where rn=1

answered Oct 27 '20 at 09:13

Fahmi

37,315
5
22
31

Thank You so much, it worked for me appreciated your help. – Ajay Oct 27 '20 at 09:36
@Ajay, happy to help you. Please mark it as accepted answer :) – Fahmi Oct 27 '20 at 09:37
how can remove displaying rn=1 in the result? as it is not required – Ajay Oct 27 '20 at 09:40

Mysql: How can i join two tables and find the revenue of each company for latest year

1 Answers1