Solved

Checking for duplicate valiues in table

Posted on 2014-03-01
2
113 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
ID: 39897277
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
ID: 39897384
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

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.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

777 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