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

Improve this question
11
  • 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, 2020 at 8:57
  • @Akina thanks man, I tried and didn't get the solution , that's why I posted here.
    – rkrathor
    Jan 16, 2020 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, 2020 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, 2020 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, 2020 at 9:36

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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, 2020 at 11:45
  • @Akina thanks man, I thought it will take extra time, will change sure. thanks again.
    – rkrathor
    Jan 17, 2020 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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