Solved

Display Duplicate Records SQL

Posted on 2009-05-07
9
299 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 100 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 25 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 100 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now