Solved

Checking for duplicate valiues in table

Posted on 2014-03-01
2
111 Views
Last Modified: 2014-03-07
I have a table Trn_OrderItemAdvise which has fields Episodeid, Adviseserialno

For each episode, we have n no of Adviseserialno which is incrementing by 1

For ex Epsiodeid 1 has adv serial no as follows

   1    1
   1     2
    1    3
   1     4
         etc
This advise serialno gets reset for each episode
i,e  episode id 2 has again adv serial no starting from 1, 2 ,3 ......

Now i have to find For each episode any duplicate adviseserialnos are present

i, e if episode 2 has 1,2,2,3,4, ...   i need to have  
  episode 2 selected with count  of 1 duplicate rows

Can experts advise on how to do this
0
Comment
Question by:venkataramanaiahsr
2 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
Comment Utility
I think something like this will work.

SELECT Episodeid, Adviseserialno, COUNT(Adviseserialno) as counter
FROM  Trn_OrderItemAdvise
GROUP BY Episodeid
HAVING COUNT(Adviseserialno) > 1
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
I think something like this will work.
Unfortuantely, that is going to produce an error.  You cannot include a column (Adviseserialno) in a SELECT that is not in an aggregate function and is not included in the GROUP BY clause.

I think you mean (no points please):
SELECT  Episodeid,
        Adviseserialno,
        COUNT(*) AS counter
FROM    Trn_OrderItemAdvise
GROUP BY Episodeid, Adviseserialno
HAVING  COUNT(*) > 1

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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