Reputation: 40
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
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
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
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;
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;
Upvotes: 1