Hi Im sure the answer is obvious but I cant see it I have a table of events (they are actually radio meteor events) This table has amongst other things event_id which is unique Date time contributor in practical terms the event may be repeated several times for any given event as different contributors may have records the same event. So im looking for a query that will return all records were the date and time match Pointers really appreciated. John B
I created a fiddle for your data - take a look at http://sqlfiddle.com/#!9/4d866/3/0 but basically, this should do the trick select event_id, eventdate, eventtime, contributor_id , (select count(event_id) from rme as r2 where rme.eventdate = r2.eventdate and rme.eventtime = r2.eventtime group by rme.eventdate, rme.eventtime) as counter from rme having counter > 1 Code (markup):
sorry further to this, it works at expected but is slowww Currently I have 2000 records and it takes about 4 seconds - I fear when the record set grows it will take to long, potentially I could end up with several hundred thousand records John B
It's using a subquery. I can suggest alternatives but I'm not sure what you're trying to achieve with this. If you could explain what the result is meant to mean to the people who see it I might be able to help a bit more. At the moment it looks like you are trying to say "We got multiple reports sent in by these guys" If that's the case then a simple looping through results will do the trick I'll check back in the morning (midnight here in NZ)
ok so I record meteor events and store them in a table called events, a record looks like this Event_ID - 8349 Date - 2017/01/01 Time - 00:01:23 Signal - -74.80 Noise - -92.00 Frequency - 2231 Duration - 2.00 Contributor - 1 Event_ID is unique and its the primary key so contributor 1 may have several thousand events for say January 2017 Now contributor 2 will alos have several thousand events for January 2017 and some will be the same as contributor 1 ie he may have this event Event_ID - 14367 Date - 2017/01/01 Time - 00:01:23 Signal - -74.80 Noise - -92.00 Frequency - 2231 Duration - 2.00 Contributor - 2 as the project increases there will be more and more contributors so its possible that multiple contributors will have recorded the same event What I want to produce is a report that shows identical events and by identical i mean were the date and time match the result using the data above would bring back 2 records but from different contributors Hope this makes sense John B
I'd be changing the way you store your data. Effectively you are deduping the database and that is always resource heavy. If you query the database with select date, time count(event_id) as counter from event_table group by date, time having counter > 0 Code (markup): you'll get what you want - the number of submissions for an event however, when you save the report you should be looking to see if the event has already been reported and create a new record in a separate table Events - event_id - date - time Reported By - report_id - user_id - event_id So you'd end up with only one record in Events per meteor event but lots of records in reported by In NZ we have a site that displays earthquakes using a feed from seismic sciencey stuff but the public can register that they "felt that". Their situation is similar to yours except that you need to create the Event as well. In fact, I'd show the list of most recent meteors and give your users the opportunity to click "I saw it too" so that reporting is quicker and simpler.
Even if you can write a good query, it seems you may still have a problem. The potential problem appears to be that different contributors may record different times for the same event. Additionally, contributors in different time zones may record different dates. The date/time zone issue may not be a big one, but the possibility of different times seems to be. Is this not so?
You have a point however all event are recorded in UTC and and within the same time zone so it should be ok