mysqlsqlsql-query-store

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

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
Solution

  • 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