Self-correlating removes duplicate data from a group

Source: Internet
Author: User

Database environment: SQL SERVER 2005

The data for the existing table is as follows, the ID is the primary key, and P1,P2 is the string type, if the value of the P1,p2 field of the current row equals another row

The value of the field P2,P1, the 2 rows are recorded as a group. For example, id=1 and id=5 belong to the same set of data. The same set of data shows only the smallest ID

The row record, no group of data is displayed.

Implementation ideas:

The table is self-correlating leftist, assuming that the alias of the table is a, B, associated with the ID, and the associated result set is filtered. If the b.id is empty, it is reserved,

If b.ID is not empty, only records with a.id smaller than b.id are retained.

Implementation of SQL script:

/*1. Data Preparation*/ withx0 as(SELECT   1  asID,'A'  asP1,'B'  asP2/*UNION all SELECT 0 as ID, ' A ' as P1, ' B ' as P2*/               UNION  All               SELECT   2  asID,'C'  asP1,'D'  asP2UNION  All               SELECT   3  asID,'E'  asP1,'F'  asP2UNION  All               SELECT   4  asID,'D'  asP1,'C'  asP2UNION  All               SELECT   5  asID,'B'  asP1,'A'  asP2UNION  All               SELECT   6  asID,'H'  asP1,'J'  asP2UNION  All               SELECT   7  asID,'T'  asP1,'U'  asP2UNION  All               SELECT   8  asID,'J'  asP1,'H'  asP2/*UNION all SELECT 9 as ID, ' I ' as P1, ' L ' as P2 UNION all SELECT as ID, ' J ' as P1, ' K ' as p2*/             ),/*2. Go to the heavy*/X1 as(SELECTID, p1, p2 from(SELECTID, p1, p2, Row_number () Over(PARTITION byP1, p2ORDER  byID) asRN fromx0) TWHERERn= 1             )    /*3. Evaluation*/    SELECTa.id, A.P1, A.p2 fromX1 a Left JOINX1 B onB.p1=A.P2 andB.p2=A.P1WHEREb.ID is NULL            ORa.ID<b.ID
View Code

The effect of the final implementation

There are also netizens proposed by ASCII to achieve, his implementation of SQL script is as follows:

 withC1 as(SELECT   1  asID,'A'  asP1,'B'  asP2/*UNION all SELECT 0 as ID, ' A ' as P1, ' B ' as P2*/               UNION  All               SELECT   2  asID,'C'  asP1,'D'  asP2UNION  All               SELECT   3  asID,'E'  asP1,'F'  asP2UNION  All               SELECT   4  asID,'D'  asP1,'C'  asP2UNION  All               SELECT   5  asID,'B'  asP1,'A'  asP2UNION  All               SELECT   6  asID,'H'  asP1,'J'  asP2UNION  All               SELECT   7  asID,'T'  asP1,'U'  asP2UNION  All               SELECT   8  asID,'J'  asP1,'H'  asP2/*UNION all SELECT 9 as ID, ' I ' as P1, ' L ' as P2 UNION all SELECT as ID, ' J ' as P1, ' K ' as P2*/), C2 as(SELECT   MIN(ID) asmin_id fromC1GROUP  by ASCII(p1)+ ASCII(p2))SELECTC1.*     fromC1JOINC2 onId=min_id
View Code

At first glance, it seems that the same needs can be achieved. In fact, there are 2 problems with this notation:

1. If P1,P2 is more than one character, the ASCII method will only take the first character of the ASCII

2.ASCII (' A ') +ascii (' D ') =ascii (' B ') +ascii (' C '), for such data, cannot be distinguished in ASCII way

(End of this article)

Self-correlating removes duplicate data from a group

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.