Mysql – Filter the query result

distinctgreatest-n-per-groupjoin;MySQL

I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report.

table 1 reports

enter image description here

Table 2 Company

enter image description here
Here is the query

SELECT c.CompanyID,c.`CategoryID`,c.`CompanyName`,  r.`CreatedDate`,r.`ReportID` FROM `company`c
JOIN reports r ON r.CompanyID = c.CompanyID
WHERE r.ProductServiceID= 14 AND c.RegionID=1 AND c.IsActive = 1
ORDER BY r.`ReportID` DESC ;

Result 1

enter image description here

I want to remove the 2nd duplicate ComapnyID in result

I have tried these queries

Distinct 
Inner join
Group by with having

if I change the query and get distinct data than the order gets change which I don't want.

Result 2

enter image description here
What am I missing here? Can any of you resolve this issue?

Thanks

Best Answer

By @Akin's help I make this query

SELECT c.CompanyID,  c.`CompanyName`, c.`CompanyCode`, c.`URLPart` FROM `company` c
JOIN reports r ON r.CompanyID = c.CompanyID
WHERE r.ProductServiceID= 14 AND c.RegionID=1 AND c.IsActive = 1
GROUP BY c.CompanyID
ORDER BY MAX(r.`CreatedDate`) DESC

which solve the issue.

Thank you very much @Akin.

Related Question