Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Display Duplicate Records SQL

Posted on 2009-05-07
9
Medium Priority
?
327 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:ITHELPME
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24327367
i think it is because, there exists no duplicate records with the same "  FCode,  NAVDate, NAVTime, FSCode " 
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24327381
yes, I agree, there may be duplicate records for FSCODE but not duplicate for  FCode,  NAVDate, NAVTime, FSCode

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 300 total points
ID: 24327419
Hope this helps you out:

Note : This will work in SQL Server 2005 and above and not in SQL Server 2000.
Revert if you are using SQL Server 2000.
SELECT FCode,  NAVDate, NAVTime
FROM (
SELECT  FCode,  NAVDate, NAVTime,
ROW_NUMBER() OVER ( PARTITION BY NAVDate, NAVTime ORDER BY NAVDate, NAVTime) rnum
FROM tblclassnav
WHERE navtime >= '1899-12-30 23:59:59.000' ) temp
WHERE rnum > 1

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ITHELPME
ID: 24327471
I am using SQL 200, it does not recognise ROW_NUMBER

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

Assisted Solution

by:RiteshShah
RiteshShah earned 75 total points
ID: 24327480
are you running this and don't get results?

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

Author Comment

by:ITHELPME
ID: 24327535
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

0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 300 total points
ID: 24327546
If it is SQL Server 2000 then try this one out:
SELECT  t1.FCode,  t1.NAVDate, t1.NAVTime
FROM tblclassnav t1, (
SELECT  NAVDate, NAVTime,
 COUNT(NAVTime) AS NumOccurrences
FROM tblclassnav
GROUP BY NAVDate, NAVTime
HAVING ( COUNT(*) > 1 )
and navtime >= '1899-12-30 23:59:59.000') t2
where t1.NAVDate = t2.NAVDate
and t1.NAVTime = t2.NAVTime

Open in new window

0
 

Author Comment

by:ITHELPME
ID: 24327704
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
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24327834
ITHELPME,
     Kindly explain your comment more so that I can help you out.
Have you tried my query in comment no 24327546
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question