SQL Saved search Duplicate ID: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
| Line 1: | Line 1: | ||
These queries finds duplicates | These queries finds duplicates. They are added to V6.0 as Saved searches with a syntax that works as Saved search <br/> | ||
'''Accident ID''' | '''Accident ID''' | ||
<pre> | <pre> | ||
| Line 55: | Line 55: | ||
ORDER BY | ORDER BY | ||
location.location_localid ASC | location.location_localid ASC | ||
</pre> | |||
'''Location name''' (not a Saved search) | |||
<pre> | |||
SELECT | |||
trim(location.locationname), | |||
Count(location.location_guid) | |||
FROM | |||
location | |||
GROUP BY | |||
trim(location.locationname) | |||
HAVING | |||
Count(location.location_guid) > '1' | |||
ORDER BY | |||
trim(location.locationname) ASC | |||
</pre> | </pre> | ||
'''MRE ID''' | '''MRE ID''' | ||
| Line 70: | Line 84: | ||
mre.mre_localid ASC | mre.mre_localid ASC | ||
</pre> | </pre> | ||
'''QA''' | '''QA ID''' | ||
<pre> | <pre> | ||
SELECT | SELECT | ||
| Line 83: | Line 97: | ||
ORDER BY | ORDER BY | ||
qa.qa_localid ASC | qa.qa_localid ASC | ||
</pre> | |||
'''Victim ID''' | |||
<pre> | |||
SELECT | |||
victim.victim_localid, | |||
Count(victim.victim_guid) | |||
FROM | |||
victim | |||
GROUP BY | |||
victim.victim_localid | |||
HAVING | |||
Count(victim.victim_guid) > '1' | |||
ORDER BY | |||
victim.victim_localid ASC | |||
</pre> | |||
'''Victim First name, Last name and Date of birth''' This query may need to be adopted if the country use CDFs or ''Age'' | |||
<pre> | |||
SELECT | |||
trim(victim.givenname), | |||
trim(victim.familyname), | |||
victim.dateofbirth, | |||
Count(victim.victim_guid) | |||
FROM | |||
victim | |||
GROUP BY | |||
trim(victim.givenname), | |||
trim(victim.familyname), | |||
victim.dateofbirth | |||
HAVING | |||
Count(victim.victim_guid) > '1' | |||
ORDER BY | |||
trim(victim.givenname) ASC, | |||
trim(victim.familyname) ASC, | |||
victim.dateofbirth ASC | |||
</pre> | </pre> | ||
[[Category:NoPublic]] | [[Category:NoPublic]] | ||
[[Category:SQL Queries]] | [[Category:SQL Queries]] | ||
Revision as of 06:45, 13 February 2014
These queries finds duplicates. They are added to V6.0 as Saved searches with a syntax that works as Saved search
Accident ID
SELECT accident.accident_localid, Count(accident.accident_guid) FROM accident GROUP BY accident.accident_localid HAVING Count(accident.accident_guid) > '1' ORDER BY accident.accident_localid ASC
Hazard ID
SELECT hazard.hazard_localid, Count(hazard.hazard_guid) FROM hazard GROUP BY hazard.hazard_localid HAVING Count(hazard.hazard_guid) > '1' ORDER BY hazard.hazard_localid ASC
HazRed ID
SELECT hazreduc.hazreduc_localid, Count(hazreduc.hazreduc_guid) FROM hazreduc GROUP BY hazreduc.hazreduc_localid HAVING Count(hazreduc.hazreduc_guid) > '1' ORDER BY hazreduc.hazreduc_localid ASC
Location ID
SELECT location.location_localid, Count(location.location_guid) FROM location GROUP BY location.location_localid HAVING Count(location.location_guid) > '1' ORDER BY location.location_localid ASC
Location name (not a Saved search)
SELECT trim(location.locationname), Count(location.location_guid) FROM location GROUP BY trim(location.locationname) HAVING Count(location.location_guid) > '1' ORDER BY trim(location.locationname) ASC
MRE ID
SELECT mre.mre_localid, Count(mre.mre_guid) FROM mre GROUP BY mre.mre_localid HAVING Count(mre.mre_guid) > '1' ORDER BY mre.mre_localid ASC
QA ID
SELECT qa.qa_localid, Count(qa.qa_guid) FROM qa GROUP BY qa.qa_localid HAVING Count(qa.qa_guid) > '1' ORDER BY qa.qa_localid ASC
Victim ID
SELECT victim.victim_localid, Count(victim.victim_guid) FROM victim GROUP BY victim.victim_localid HAVING Count(victim.victim_guid) > '1' ORDER BY victim.victim_localid ASC
Victim First name, Last name and Date of birth This query may need to be adopted if the country use CDFs or Age
SELECT trim(victim.givenname), trim(victim.familyname), victim.dateofbirth, Count(victim.victim_guid) FROM victim GROUP BY trim(victim.givenname), trim(victim.familyname), victim.dateofbirth HAVING Count(victim.victim_guid) > '1' ORDER BY trim(victim.givenname) ASC, trim(victim.familyname) ASC, victim.dateofbirth ASC