Wednesday, October 11, 2023

finding duplicates to fix SQL query - ad hoc query in Moodle

There was an issue once again with the feedback ad-hoc Moodle report - checking the query in DBeaver, the database returns an error "Subquery returns more than one row" for some dates. Eg Oct 1.

Via https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table

Found the duplicate timestamps with

select timemodified, count(*) from ourprefix_feedback_completed
GROUP BY timemodified
HAVING COUNT(*) > 1

1690263769    2
1696119639    5


Have changed the timestamps to increment the duplicates by one, so that the query works without issues.

 

No comments:

Post a Comment