I have a table with 3 columns, a record_num, a date field, and a description. In the date field there can be some rows that have a distinct date and others that have the same date. I wanted to take a look at all the rows that have dates that are at least duplicated. I thought this query would work but it produces results that include unique dates.

SELECT * FROM TIME_TABLE
WHERE
  EXTRACT(MONTH FROM DATE_FIELD ) = EXTRACT(MONTH FROM DATE_FIELD )
  AND
  EXTRACT(DAY FROM DATE_FIELD ) = EXTRACT(DAY FROM DATE_FIELD )
ORDER BY RECORD_NUM, DATE_FIELD, DESCRIPTION

Hello,

Ok the problem is that your where clause is true for every record. If I understand you want only records where the count of the number of records on any specific date is 2 or greater. If that is so what you would need to do is create a sub query to determine which records you want and then generate the output using those records. The way I do this is a three step process. First create the query to get the dates with count of how many on each date.

SELECT record_date, count(*)  
from TIME_TABLE 
group by record_date

Verify your data and then limit the selection to only records where the count on that day is > 1

SELECT record_date 
from TIME_TABLE 
group by record_date 
HAVING count(*) > 1

Finally get your records based on the dates you determined.

SELECT * FROM TIME_TABLE
WHERE record_date in (SELECT record_date 
from TIME_TABLE 
group by record_date 
HAVING count(*) > 1) 
order by record_date

Thanks for your comment. I see how I was thinking about this incorrectly.
I was able to get to your step 2 successfully and see a list of dates that have more than 1 entries and the number of entries that a given date has.

When I tried the last example you provided the database editor went into a long pause and did not come out after 15 min.
In the file that I tested it on 11,029 records so I am not sure how long this should take, but I suspect that application hung.

I will try again and let it run for an extended period and see if it comes out with results.

Thanks!
WBR

Wow! Your query does work. It took 45 min on my system.

I hope you can reduce the query time by creating an index on record_date.

Msasif is correct about the query time being reduced. One other thing are you storing the date as a date only or a datetime. If it is a date time that would cause it to take a long time because every record would have a unique datetime and you would have very few exact matches but still some.

The data is stored as a Date, not a DateTime. I can not create an index on the date field since they are not unique.

Is there a way to create an index on a non-unique Date field?

Query to create index on Date field

ALTER TABLE TIME_TABLE ADD INDEX record_date (record_date);

Thanks! It hums now. I am down to 3 min.

Try the following, also confirm that you are getting the correct records and query time is reduced.

SELECT * 
FROM TIME_TABLE 
GROUP BY record_date 
HAVING count(*) > 1
ORDER BY record_date
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.