<div>
I think something like this will work.<br />
<br />
SELECT Episodeid, Adviseserialno, COUNT(Adviseserialno) as counter<br />
FROM &nbsp;Trn_OrderItemAdvise<br />
GROUP BY Episodeid<br />
HAVING COUNT(Adviseserialno) &gt;&nbsp;1
</div>


I think something like this will work.

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

<div>
<div class="content wysiwyg-content">
I have a table Trn_OrderItemAdvise which has fields Episodeid, Adviseserialno<br />
<br />
For each episode, we have n no of Adviseserialno which is incrementing by 1 <br />
<br />
For ex Epsiodeid 1 has adv serial no as follows<br />
<br />
&nbsp; &nbsp;1 &nbsp; &nbsp;1<br />
&nbsp; &nbsp;1 &nbsp; &nbsp; 2<br />
&nbsp; &nbsp; 1 &nbsp; &nbsp;3<br />
&nbsp; &nbsp;1 &nbsp; &nbsp; 4 <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;etc<br />
This advise serialno gets reset for each episode<br />
i,e &nbsp;episode id 2 has again adv serial no starting from 1, 2 ,3 ......<br />
<br />
Now i have to find For each episode any duplicate adviseserialnos are present <br />
<br />
i, e if episode 2 has 1,2,2,3,4, ... &nbsp; i need to have &nbsp;<br />
&nbsp; episode 2 selected with count &nbsp;of 1 duplicate rows<br />
<br />
Can experts advise on how to do this
</div>
</div>


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

Checking for duplicate valiues in table

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.