June 30, 2015 at 2:01 pm
In my asp.net project there are about 100 drop down list.
I created a table to store data for drop down list in which including [DropdownID], [OrderSequece] and [Description] three columns. The sample like below.
Data was input manually by a user.
How to code to find out duplicate [OrderSequence]?
----------------
DropdownID--OrderSequece--Description
1-------------0--------------AAA
1-------------1--------------BBB
2-------------0--------------YYY
2-------------1--------------XXX
2-------------2--------------QQQ 'DUPLICATE OrderSequece
2-------------2--------------WWW 'DUPLICATE OrderSequece
2-------------3--------------RRR
June 30, 2015 at 2:22 pm
One way...
SELECT *
FROM
(SELECT DDid
, Seq
, Descrip
, ROW_NUMBER() OVER (PARTITION BY DDid, Seq ORDER BY DDid, Seq) AS rn
FROM
(SELECT 1 As DDid, 0 AS Seq, 'AAA' AS Descrip
UNION ALL
SELECT 1, 1, 'BBB'
UNION ALL
SELECT 2, 0, 'YYY'
UNION ALL
SELECT 2, 1, 'XXX'
UNION ALL
SELECT 2, 2, 'QQQ'
UNION ALL
SELECT 2, 2, 'WWW'
UNION ALL
SELECT 2, 3, 'RRR') data ) x
WHERE x.rn>1
Another:
SELECT DDID, Seq, COUNT(Descrip)
FROM
(SELECT 1 As DDid, 0 AS Seq, 'AAA' AS Descrip
UNION ALL
SELECT 1, 1, 'BBB'
UNION ALL
SELECT 2, 0, 'YYY'
UNION ALL
SELECT 2, 1, 'XXX'
UNION ALL
SELECT 2, 2, 'QQQ'
UNION ALL
SELECT 2, 2, 'WWW'
UNION ALL
SELECT 2, 3, 'RRR') data
GROUP BY DDID, Seq
HAVING COUNT(Descrip)>1;
June 30, 2015 at 2:41 pm
You can find them using CTE.
with cte_Test
as (
select [DropdownID]
, [OrderSequence]
, COUNT(*) CountDuplicated
, MIN([Description]) FirstDescription
from TestData
group by [DropdownID]
, [OrderSequence]
having COUNT(*) > 1
)
-- select * from cte_Test
select td.*
from TestData td
inner join cte_Test ct on ct.DropdownID = td.DropdownID and ct.OrderSequence = td.OrderSequence
With this you can define wich records will be deleted.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply