Solved

Display Duplicate Records SQL

Posted on 2009-05-07
9
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

624 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