February 12, 2013 at 3:17 am
Hello Guys;
I have a table which contains duplicate entries (almost all) but with a differentiating PK & another 1 column.
Below is the sample of what I have
IDNarrationRenewalCountRenewed
1ABC10
2ABC10
3ABC00
4PQR10
5PQR00
Now, I need to get distinct entries from this table as given below
IDNarrationRenewalCountRenewed
3ABC00
5PQR00
The idea is for me to set Renewed column = 1 for the rest of the records in the table.
February 12, 2013 at 3:27 am
One way
declare @table table (id int, narration char(3), renewalcount int, renewed int)
insert into @table values
(1,'ABC',1,0),
(2,'ABC',1,0),
(3,'ABC',0,0),
(4,'PQR',1,0),
(5,'PQR',0,0)
SELECT
t1.ID,
t1.Narration,
t1.RenewalCount,
t1.Renewed
FROM
@table t1
INNER JOIN
(
SELECT
MAX(ID) AS MaxID,
Narration
FROM
@table
GROUP BY
Narration
) AS t2
ON
t1.ID = t2.MaxID
February 12, 2013 at 3:31 am
Thank you for the prompt response.
I actually didn't get the expected result but it did gave me an idea. Below is what I did to get the right result:
SELECT ID_PolicyMaster, Narration, RenewalCount, Renewed, RenewedID
FROM PolicyMaster WHERE ID_PolicyMaster IN
(
SELECT MAX(ID_PolicyMaster) AS MaxID_PolicyMaster
FROM PolicyMaster
GROUP BY Narration
HAVING (COUNT(Narration) > 1)
)
ORDER BY Narration
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply