Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Display Duplicate Records SQL

Posted on 2009-05-07
9
Medium Priority
?
324 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

688 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