Solved

Display Duplicate Records SQL

Posted on 2009-05-07
9
302 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

15 Experts available now in Live!

Get 1:1 Help Now