Query help

[rouse]

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

[rch1231]

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

[rouse]

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

[rouse]

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

[mwasif]

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

[rch1231]

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.

[rouse]

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.

[rouse]

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

[mwasif]

Query to create index on Date field

ALTER TABLE TIME_TABLE ADD INDEX record_date (record_date);

[rouse]

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

[mwasif]

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