0

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

  • Marked records are NOT duplicates. The records are duplicates if and only if ALL fields in them have the same values. If at least one filed have different values - that pair is NOT a duplicate. I want to remove the duplicate id in result If you want to select only one record from such pair (or more) you must create/formulate a criteria which allows to select what record must be stored and what record(s) must be eliminated. – Akina Jan 16 at 8:57
  • @Akina thanks man, I tried and didn't get the solution , that's why I posted here. – rkrathor Jan 16 at 9:02
  • Nevertheless I do not see clearly and unambiguously formulated criteria which allows to determine what record from "duplicates" must be selected. Until it occures nobody can help you. – Akina Jan 16 at 9:06
  • @Akina I change the query and remove the duplicate values but now why that order by is not working? – rkrathor Jan 16 at 9:25
  • but now why that order by is not working? What ordering? where, in what screenshot? I don't understand... be more precise, please. – Akina Jan 16 at 9:36
0

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.

  • I'd recommend you to use GROUP BY c.CompanyID, c.CompanyName, c.CompanyCode, c.URLPart. This will not decrease performance, but will avoid the problem with "not full group by" error if you'll change SQL mode of your server or 'll update its version in future. – Akina Jan 16 at 11:45
  • @Akina thanks man, I thought it will take extra time, will change sure. thanks again. – rkrathor Jan 17 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.