asked on

Display Duplicate Records SQL

Hi

I am generating a query to show records that are duplicate. But I need to add in an extra field, FSCode, when I add this in I get nothing returned, but I know there are records that are duplicated for this.
Any help would be great.
Thanks

SELECT  FCode,  NAVDate, NAVTime,
 COUNT(NAVTime) AS NumOccurrences
FROM tblclassnav
GROUP BY FCode,  NAVDate, NAVTime
HAVING ( COUNT(*) > 1 )
and navtime >= '1899-12-30 23:59:59.000'

Open in new window

Aneesh

i think it is because, there exists no duplicate records with the same " FCode, NAVDate, NAVTime, FSCode "

RiteshShah

yes, I agree, there may be duplicate records for FSCODE but not duplicate for FCode, NAVDate, NAVTime, FSCode

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITHELPME

ASKER

I am using SQL 200, it does not recognise ROW_NUMBER

For the answer above there are duplicate records for FCode, FSCode, NAVDate, NAVTime

SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITHELPME

ASKER

When I run the above I get nothing returned, but when I take out FSCode I get results

I do have dupliacte records for these on NAVTime as NAVTime is a different Time but all others are the same

SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITHELPME

ASKER

it does sort of

Sometimes I will have more than one record for FCode, NAVDate, NAVTime

But I should not have more than one record for FCode, FSCode, NAVDate, NAVTime i this makes sense

Raja Jegan R

ITHELPME,
Kindly explain your comment more so that I can help you out.
Have you tried my query in comment no 24327546