CODEWITHSUNDEEP
mysqlsql
arunmah
arunmah

Reputation: 40

Get latest records by two column in a query

I have a table named 'reports' as below:

id     company    year    quarter
1      A          2008    1
2      A          2008    2
3      A          2008    3
4      A          2008    4
5      A          2009    1
6      A          2009    2
7      B          2008    1
8      B          2008    2
9      B          2008    3
10     B          2008    4
11     B          2009    1
12     B          2009    2

The table contains year and quarter. Each year has four quarters. The latest record is determined by year and quarter column. So the record with highest year and highest quarter in that year is the latest record. I would like to get the latest report for each company in a single query. In this example, this would be the result.

id     company    year    quarter
6      A          2009    2
12     B          2009    2

I have tried to achieve it by using following query:

select * from reports
inner join ( select company, max( year ) AS year, max( quarter ) AS quarter 
             from reports group by company ) as r 
    on r.company = reports.company 
        and r.year = reports.year 
        and r.quarter = reports.quarter

Upvotes: 0

Views: 60

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I would simply do:

select t.*
from t
where (t.year, t.quarter) = (select t2.year, t2.quarter
                             from t t2
                             where t2.company = t.company
                             order by t2.year desc, t2.quarter desc
                             limit 1
                            );

Here is a rextester.

I cannot think of a simpler way to accomplish this. And with an index on (company, year, quarter), this should have better performance than alternatives.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use co-related subquery

    select year, company,max(id),max(quarter) from reports t
   where t.year in ( 
      select max(year)
     from reports t1 
    where t1.company=t.company 
        group by t1.company
                   )
                   group by year,company

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522807

The pre-MySQL 8 way uses a subquery to identify the latest records for each company:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT company, MAX(year + quarter / 4.0) AS max_year
    FROM yourTable
    GROUP BY company
) t2
    ON t1.company = t2.company AND
       (t1.year + t1.quarter / 4.0) = t2.max_year;

enter image description here

Demo

Your current attempt has a problem, because it will find the max value for quarter without regard to the particular year. That is, it would find a max quarter of 4 for both companies, even though in their latest respective years (2009), the max quarters were actually 2. I get around this by forming a decimal year, which can be, e.g. 2009.5 for the second quarter of 2009.

Beginning with MySQL 8+ we can take advantage of the ROW_NUMBER analytic function:

SELECT id, company, year, quarter
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY company
        ORDER BY year DESC, quarter DESC) rn
    FROM yourTable
) t
WHERE t.rn = 1;

Demo

Upvotes: 1

Related Questions