none
Get first record value from each group using T-SQL

    Question

  • Hi All,

    I have one table with 3 columns, say Name  as varchar, ID1 as int, ID2 as int datatypes.

    Create table:

    Create table Sample
    (
     Rec_Id Int Not null,
     Name varchar(30) null,
     ID1 int null,
     ID2 int null,
    CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED
    (
     [Rec_Id] ASC
    )


    Insert Statement:

    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A',1,2)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 2,3)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 3 ,1)

     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B', 1 , 2)
    Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  2 , 3)
    Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 3 , null)

     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C', 1 , 2)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 2 , 3)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 3 , 4)
     Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 5 , 1 )

    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 2 , 3)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 3 , 4)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 4 , 3)

    My source data looks like this..

    Rec_Id Name ID1   ID2
      1           A        1     2       
      2           A        2     3
      3           A        3     1

      4           B        1     2
      5           B        2     3
      6           B        3    null    --> Need to display this row in the output, because 1 is missing in ID2

      7           C        1     2
      8           C        2     3
      9           C        3     4
     10          C            1     --> Need to display this row in the output, because 4 is missing after 3 in ID1

     11          D        2     3
     12          D        3     4
     13          D        4     3    --> Need to display this row in the output, because 2 is missing in ID2


    My Output should look like below:

    Rec_Id  Name  ID1   ID2
    6                B       3    
    10              C       5      1
    13              D       4      3

    So, I need t-sql to get above output.

    Thanks in advance,
    RH


    sql

    Thursday, April 10, 2014 9:41 PM

Answers

  • Create table Sample
     (
      Rec_Id Int Not null,
      Name varchar(30) null,
      ID1 varchar(10) null,
      ID2 varchar(10) null,
     CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED 
    (
      [Rec_Id] ASC
     ))
      
    
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31')  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
     
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    'B1Y35' , 'B2Y15')
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  'B2Y15', 'B5Y13')
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
     
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   'C5Z19' , 'C5B12')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ')
      Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19')  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)
    
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17')  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)
     
    ;with mycte as
    (select Rec_Id,name, id1, id2  
    ,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 
    ,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
     
     
    from sample )
    , mycte1 as(
    select m0.Rec_id,m0.name, m0.id1,  m0.id2 , Coalesce (m1.id2, m2.id2,'') id2_2, m0.rn2
    , Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'')   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
    from mycte m0 
    Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
    Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
    )
    
    Select Rec_id,name, id1,id2 
    from mycte1
    WHERE rn2=1 AND cnt>0
    Order by Rec_id
    
    
    
    drop table sample

    • Marked as answer by sql9 Friday, April 11, 2014 8:32 PM
    Friday, April 11, 2014 7:06 PM
  • What version of SQL Server are you using?

    WITH C1 AS (
    SELECT
    	Name,
    	CASE 
    	WHEN FIRST_VALUE(ID1) OVER(PARTITION BY Name ORDER BY Rec_Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) <> 
    		ISNULL(LAST_VALUE(ID2) OVER(PARTITION BY Name ORDER BY Rec_Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 'BROKEN_LOOP') THEN 1
    	WHEN ID1 <> LAG(ID2, 1, ID1) OVER(PARTITION BY Name ORDER BY Rec_Id) THEN 1
    	ELSE 0
    	END AS broken_loop
    FROM
    	#sample
    )
    , C2 AS (
    SELECT
    	Name,
    	MAX(broken_loop) AS bl
    FROM
    	C1
    GROUP BY
    	Name
    )
    SELECT
    	T.*
    FROM
    	C2
    	CROSS APPLY
    	(
    	SELECT TOP (1)
    		*
    	FROM
    		#sample AS S
    	WHERE
    		S.Name = C2.Name
    	ORDER BY
    		S.Rec_Id DESC
    	) AS T
    WHERE
    	C2.bl = 1;
    GO

    The idea was to identify sequences that are broken either because previous ID2 does not match current ID1 or the first ID1 does not match last ID2 (closing the loop).

    Then bring the last row for each name with a broken sequence.


    AMB

    Some guidelines for posting questions...


    Friday, April 11, 2014 7:23 PM

All replies

  • I am sorry, one small correction. My table has 4 columns not 3.

    Thanks,

    RH


    sql

    Thursday, April 10, 2014 9:45 PM
  • Thursday, April 10, 2014 9:57 PM
  • Hello,

    Based on your description, it seems that you want to get the records when the values is not connected on the "ID1" column. For example, 1,2,3 is connected but 1,2,3,5 is not. If so, just as Kalman post above, you can try to use OVER PARTITION BY. Please refer to following statements:

    with cte as (select Rec_Id,name,id1,id2,ROW_NUMBER() over(partition by name order by id1) as rn1, ROW_NUMBER() over(partition by name order by id2) as rn2 from sample ) select s1.Rec_Id,s1.name,s1.id1,s1.id2 from cte s1 inner join cte s2 on s1.name=s2.name and s1.rn1=s2.rn1+1 where s1.id1-s2.ID1>1

    Currently, I cannot get the filter logic on the "ID2" column. Can you please more details about defined the missing value?

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Friday, April 11, 2014 7:58 AM
  • Thanks Liu.

    ok, let me explain in other words...In the above example right now 'Name' column has 4 groups, Name =A, B, C ,D.

    A group- has 3 records which make one loop. I am saying it formed one loop becoz(see bold ones) 3rd row value in ID2 column(ID2=1) matches with 1st row ID1 column(ID1=1).
    Same loop concept implies for B , C and D groups.

    --A - group records:

    Name   ID1   ID2
     A              2
     A          2     3
     A          3     1

    --B - group records:

    Name  ID1   ID2
     B        1     2
     B        2     3
     B        3    null

    B group - has 3 records which is broken loop. I am saying it is broken loop becoz(see bold ones) 3rd row value in ID2 column(ID2=null) doesn't matches with 1st row ID1 column(ID1=1).


    C- group records: Same loop concept implies for C group. If there is a break in the series need to display. The query which you gave it worked for C group.


    D- group records:

    Name     ID1   ID2
     D               3
     D           3     4 
     D           4     3

    D group - has 3 records which is broken loop. I am saying it is broken loop becoz(see bold ones) 3rd row value in ID2 column(ID2=3) doesn't matches with 1st row ID1 column(ID1=2).


    I hope my explaination gives some info about missing values..Let me know if my explaination is confusing.

    Thanks in advance,
    RH


    sql

    Friday, April 11, 2014 1:59 PM
  • Hi Liu.

    I think the query which you gave it works when ID's are integers. But in my real case, the ID's are alphanumeric and "where" clause in your query will  not work for alphanumeric. Just for easy representation I have used numbers in the above example.

    Sorry for the confusion.

    Thanks,

    RH

     

    sql

    Friday, April 11, 2014 2:50 PM
  • Create table Sample
    (
     Rec_Id Int Not null,
     Name varchar(30) null,
     ID1 int null,
     ID2 int null
    )
    
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A',1,2)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 2,3)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 3 ,1)
    
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B', 1 , 2)
    Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  2 , 3)
    Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 3 , null)
    
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C', 1 , 2)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 2 , 3)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 3 , 4)
     Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 5 , 1 )
    
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 2 , 3)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 3 , 4)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 4 , 3)
    
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 14 ,'E', 2 , 3)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 15 , 'E', 3 , 4)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 16 , 'E' , 4 , 2)
    
    
    --select * from sample
    ;with mycte as
    (select Rec_Id,name,id1,id2
    ,ROW_NUMBER() over(partition by name order by id1) as rn1 
    ,ROW_NUMBER() over(partition by name order by id1 DESC) as rn2
    ,ROW_NUMBER() over(partition by name order by id1)-id1 as delta
     
    from sample )
    
     
    
    ,mycte1 as(
    select m1.Rec_Id,m1.name, m1.ID1 as ID1_1, m2.ID1 as ID1_2, m1.ID2 
    , m1.delta as delta1, m2.delta as delta2
    from mycte m1 inner join mycte m2 on m1.name =m2.name and m1.rn1=m2.rn2
    WHERE m1.rn2=1
    )
    
    Select Rec_Id,Name,ID1_1 as ID1,  ID2  
    FROM mycte1
    WHERE (ID1_2<>ID2 or ID2 IS NULL) or delta2<>delta1 
    
    
    
    
    drop table sample

    Friday, April 11, 2014 4:11 PM
  • Thanks Li for the quick reply. It gives the correct output, but there is a problem with your query while getting Delta value. I can't do minus operation on ID1 values.

    "ROW_NUMBER()over(partitionbynameorderbyid1)-id1asdelta"

    My ID values are not numbers in real scenario. They are AlphaNumeric. Just for easy representation I have used numbers in the above example.

    Thanks,

    RH


    sql

    Friday, April 11, 2014 4:36 PM
  • Can you modify your sample table and let's see whether we can work out something? Thanks.
    Friday, April 11, 2014 4:44 PM
  • Thanks Li.

    Here is the modified sample data.

    Create table:

    Create table Sample
     (
      Rec_Id Int Not null,
      Name varchar(30) null,
      ID1 varchar(10) null,
      ID2 varchar(10) null,
     CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED
    (
      [Rec_Id] ASC
     ))

     

    Insert Statement:

    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', A1X31,A2XX1)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', A2XX1,B2C45)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', B2C45 ,A1X31)  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
     
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    B1Y35 , B2Y15)
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  B2Y15, B5Y13)
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , B5Y13 , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
     
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   C5Z19 , C5B12)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , C5B12 , C5JPQ)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , C5JPQ , C3JPQ)
      Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , C6JPQ , C5Z19)  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)

    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', D5Z16 , D5Z17)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', D5Z17, D5Z18)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , D5Z18 , D5Z17)  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)


    So my output should look like this..

    Name      ID1      ID2

      B       B5Y13    null
      C       C6JPQ    C5Z19
      D       D5Z18    D5Z17

    Thanks,

    RH


    sql

    Friday, April 11, 2014 5:13 PM
  • Hi Li,

    I tried this SQL and I got output like this..

    SQL code:

    Select A.Rec_Id,A.Name,A.ID1,A.ID2 from
    (
    Select st.Rec_Id,st.Name,st.ID1 , st.ID2
     from Sample st
    Join Sample stt ON stt.Rec_Id=st.Rec_Id
    ) A,
    (
    Select Max(s.Rec_ID) as Rec_Id, s.Name from Sample s
    Join Sample st ON st.Rec_Id=s.Rec_Id
    Group by s.Name
    ) B
    where A.Rec_Id=B.Rec_Id

    I got below Output from the above  SQL... but my query pulls 1st record also, I don't want to show 1st record in my output becoz there is no looping issue with group-A. So I need some help to tweak this query.

    Rec_Id       Name      ID1        ID2
         3              A         B2C45     A1X31 
         6              B         B5Y13     NULL
        10             C         C6JPQ     C5Z19
        13             D         D5Z18    D5Z17

    Thanks,

    RH


    sql

    Friday, April 11, 2014 6:32 PM
  • Create table Sample
     (
      Rec_Id Int Not null,
      Name varchar(30) null,
      ID1 varchar(10) null,
      ID2 varchar(10) null,
     CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED 
    (
      [Rec_Id] ASC
     ))
      
    
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31')  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
     
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    'B1Y35' , 'B2Y15')
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  'B2Y15', 'B5Y13')
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
     
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   'C5Z19' , 'C5B12')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ')
      Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19')  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)
    
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17')  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)
     
    ;with mycte as
    (select Rec_Id,name, id1, id2  
    ,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 
    ,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
     
     
    from sample )
    , mycte1 as(
    select m0.Rec_id,m0.name, m0.id1,  m0.id2 , Coalesce (m1.id2, m2.id2,'') id2_2, m0.rn2
    , Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'')   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
    from mycte m0 
    Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
    Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
    )
    
    Select Rec_id,name, id1,id2 
    from mycte1
    WHERE rn2=1 AND cnt>0
    Order by Rec_id
    
    
    
    drop table sample

    • Marked as answer by sql9 Friday, April 11, 2014 8:32 PM
    Friday, April 11, 2014 7:06 PM
  • What version of SQL Server are you using?

    WITH C1 AS (
    SELECT
    	Name,
    	CASE 
    	WHEN FIRST_VALUE(ID1) OVER(PARTITION BY Name ORDER BY Rec_Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) <> 
    		ISNULL(LAST_VALUE(ID2) OVER(PARTITION BY Name ORDER BY Rec_Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 'BROKEN_LOOP') THEN 1
    	WHEN ID1 <> LAG(ID2, 1, ID1) OVER(PARTITION BY Name ORDER BY Rec_Id) THEN 1
    	ELSE 0
    	END AS broken_loop
    FROM
    	#sample
    )
    , C2 AS (
    SELECT
    	Name,
    	MAX(broken_loop) AS bl
    FROM
    	C1
    GROUP BY
    	Name
    )
    SELECT
    	T.*
    FROM
    	C2
    	CROSS APPLY
    	(
    	SELECT TOP (1)
    		*
    	FROM
    		#sample AS S
    	WHERE
    		S.Name = C2.Name
    	ORDER BY
    		S.Rec_Id DESC
    	) AS T
    WHERE
    	C2.bl = 1;
    GO

    The idea was to identify sequences that are broken either because previous ID2 does not match current ID1 or the first ID1 does not match last ID2 (closing the loop).

    Then bring the last row for each name with a broken sequence.


    AMB

    Some guidelines for posting questions...


    Friday, April 11, 2014 7:23 PM
  • Thanks alot Li. It worked.

    sql

    Friday, April 11, 2014 8:33 PM
  • Hi Li,

    Today User came up with two more scenariors along with above. I need your help to get logic for those two scenarios also.

    I am listing the new scenarios:

    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 14 ,'E', 'E6C16' , 'G8A65')  --> Need to display this 1st record becoz it is broken loop ( 1st row ID2='G8A65' value doesn't matches 2nd row ID1 =  'E6C17' value)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 15 , 'E', 'E6C17', 'E5C18')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 16 , 'E' , 'E6C18' , 'E6C19')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 17 , 'E' , 'E6C19' , 'E6C16') --> Need to display this 4th record just becoz 1st row is broken loop row, even though ( 4th row ID2='E6C16' value matches 1st row ID1 = 'E6C16' value)

    Insert into sample (Rec_ID,Name,ID1,ID2) values  ( 18 ,'F', 'F5U15' , 'F5U16')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 19 , 'F', 'F5U16', 'F5U17')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 20 , 'F' , 'F5U17' , 'F5U19')  --> Need to display this 3rd record becoz it is broken loop ( 3rd row ID2='F5U19' value doesn't matches 4th row ID1 =  'F5U18' value)
    Insert into sample (Rec_ID,Name,ID1,ID2) values  ( 21 ,'F', 'F5U18' , 'F5U15')   --> Need to display this 4th record becoz it is broken loop ( 4th row ID2='F5U15' value doesn't matches 5th row ID1 =  'F5U19' value)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 22, 'F', 'F5U19', 'F5U18')    --> Need to display this 5th record becoz it is broken loop ( 5th row ID2='F5U18' value doesn't matches 1st row ID1 =  'F6U15' value)

    Thanks in advance,

    RH


    sql

    Monday, April 14, 2014 8:42 PM
  • Create table Sample (  Rec_Id Int Not null,  Name varchar(30) null,  ID1 varchar(10) null,  ID2 varchar(10) null)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31')  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    'B1Y35' , 'B2Y15')
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  'B2Y15', 'B5Y13')
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   'C5Z19' , 'C5B12')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ')  --> ***** Need to display??
      Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19')  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17')  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 14 ,'E', 'E6C16' , 'G8A65')  --> Need to display this 1st record becoz it is broken loop ( 1st row ID2='G8A65' value doesn't matches 2nd row ID1 =  'E6C17' value)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 15 , 'E', 'E6C17', 'E5C18')   --> ***** Need to display???
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 16 , 'E' , 'E6C18' , 'E6C19')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 17 , 'E' , 'E6C19' , 'E6C16') --> Need to display this 4th record just becoz 1st row is broken loop row, even though ( 4th row ID2='E6C16' value matches 1st row ID1 = 'E6C16' value)
     Insert into sample (Rec_ID,Name,ID1,ID2) values  ( 18 ,'F', 'F5U15' , 'F5U16')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 19 , 'F', 'F5U16', 'F5U17')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 20 , 'F' , 'F5U17' , 'F5U19')  --> Need to display this 3rd record becoz it is broken loop ( 3rd row ID2='F5U19' value doesn't matches 4th row ID1 =  'F5U18' value)
    Insert into sample (Rec_ID,Name,ID1,ID2) values  ( 21 ,'F', 'F5U18' , 'F5U15')   --> Need to display this 4th record becoz it is broken loop ( 4th row ID2='F5U15' value doesn't matches 5th row ID1 =  'F5U19' value)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 22, 'F', 'F5U19', 'F5U18')    --> Need to display this 5th record becoz it is broken loop ( 5th row ID2='F5U18' value doesn't matches 1st row ID1 =  'F6U15' value)
    
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 23 ,'G', 'A1X31','A2XX1')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 24 ,'G', 'A2XX1','B2C45')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 25 ,'G', 'B2C45' ,'A1X31')
    
    ;with mycte as
    (select Rec_Id,name, id1, id2  
    ,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 
    ,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
      
    from sample )
    , mycte1 as(
    select m0.Rec_id,m0.name, m0.id1
    , Coalesce (m3.id1, m2.id1,'')  as id1_3, m0.id2 
    , Coalesce (m1.id2, m2.id2,'') id2_1, m0.rn2
    , Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'')   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
    from mycte m0 
    Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
    Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
    Left Join mycte m3 On  m0.name =m3.name and m0.rn1=m3.rn1-1
    
    )
    
    
    Select Rec_id,name, id1,id2 from mycte1
    WHERE  rn2=1 AND cnt>0
    UNION
    Select Rec_id,name, id1,id2 from mycte1
    WHERE id1_3<>id2 
    
    Order by Rec_id
    
    
    
    drop table sample

    Tuesday, April 15, 2014 3:35 AM
  • Thanks so much Li for the code.

    From your comments,No need to display Rec_Id=9 and No need to display Rec_Id=15, for Rec_Id=15 the Id2 (ID2='E6C18' instead of 'E5C18') value was typo mistake from my side.

    I think for not to display Rec_Id=9 , the current logic would change right? if yes please send me the updated SQL for it.

    Thanks in Advance,
    RH


    sql

    Tuesday, April 15, 2014 12:36 PM
  • The logic to show Rec_id 20 will show Rec_id 9. Please check your logic. Thanks.
    Tuesday, April 15, 2014 12:47 PM
  • I verified my logic and I am sorry. I agree with you Li.

    One final case forgot to mention. I am extremely sorry for missing this case. Please Can I get updated SQL for this case. The current SQL code worked for all other scenarios except this new case.

    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 26 ,'H', 'H1X15','H2X16')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 27 ,'H', 'H2X16','H3X17')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 28 ,'H', 'H3X17' ,'H5X19') --> Need to display this 3rd record becoz it is broken loop ( 3rd row ID2='H5X19' value doesn't matches 4th row ID1 =  'H4X18' value)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 29 ,'H', 'H4X18' ,'H5X19') --> Need to display this 4th record becoz it is broken loop ( 4th row ID2='H5X19' value matches with 3rd row ID2 =  'H5X19' value and it is repeating twice for 3rd , 4th row ID2 values)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 30 ,'H', 'H5X19' ,'H1X15')

    Thanks,

    RH


    sql

    Tuesday, April 15, 2014 2:42 PM
  • When you cut a loop, you will have two parties involved. 

    Here is my guess:

    Create table Sample (  Rec_Id Int Not null,  Name varchar(30) null,  ID1 varchar(10) null,  ID2 varchar(10) null)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31')  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    'B1Y35' , 'B2Y15')
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  'B2Y15', 'B5Y13')
     Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   'C5Z19' , 'C5B12')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ')  --> ***** Need to display??
      Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19')  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17')  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 14 ,'E', 'E6C16' , 'G8A65')  --> Need to display this 1st record becoz it is broken loop ( 1st row ID2='G8A65' value doesn't matches 2nd row ID1 =  'E6C17' value)
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 15 , 'E', 'E6C17', 'E6C18')   
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 16 , 'E' , 'E6C18' , 'E6C19')
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 17 , 'E' , 'E6C19' , 'E6C16') --> Need to display this 4th record just becoz 1st row is broken loop row, even though ( 4th row ID2='E6C16' value matches 1st row ID1 = 'E6C16' value)
     Insert into sample (Rec_ID,Name,ID1,ID2) values  ( 18 ,'F', 'F5U15' , 'F5U16')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 19 , 'F', 'F5U16', 'F5U17')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 20 , 'F' , 'F5U17' , 'F5U19')  --> Need to display this 3rd record becoz it is broken loop ( 3rd row ID2='F5U19' value doesn't matches 4th row ID1 =  'F5U18' value)
    Insert into sample (Rec_ID,Name,ID1,ID2) values  ( 21 ,'F', 'F5U18' , 'F5U15')   --> Need to display this 4th record becoz it is broken loop ( 4th row ID2='F5U15' value doesn't matches 5th row ID1 =  'F5U19' value)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 22, 'F', 'F5U19', 'F5U18')    --> Need to display this 5th record becoz it is broken loop ( 5th row ID2='F5U18' value doesn't matches 1st row ID1 =  'F6U15' value)
    
     Insert into sample (Rec_ID,Name,ID1,ID2) values ( 23 ,'G', 'A1X31','A2XX1')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 24 ,'G', 'A2XX1','B2C45')
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 25 ,'G', 'B2C45' ,'A1X31')
    
    
      Insert into sample (Rec_ID,Name,ID1,ID2) values ( 26 ,'H', 'H1X15','H2X16')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 27 ,'H', 'H2X16','H3X17')
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 28 ,'H', 'H3X17' ,'H5X19') --> Need to display this 3rd record becoz it is broken loop ( 3rd row ID2='H5X19' value doesn't matches 4th row ID1 =  'H4X18' value)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 29 ,'H', 'H4X18' ,'H5X19') --> Need to display this 4th record becoz it is broken loop ( 4th row ID2='H5X19' value matches with 3rd row ID2 =  'H5X19' value and it is repeating twice for 3rd , 4th row ID2 values)
    Insert into sample (Rec_ID,Name,ID1,ID2) values ( 30 ,'H', 'H5X19' ,'H1X15')
    
    ;with mycte as
    (select Rec_Id,name, id1, id2  
    ,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 
    ,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
      
    from sample )
    , mycte1 as(
    select m0.Rec_id,m0.name, m0.id1
    , Coalesce (m3.id1, m2.id1,null)  as id1_3, m0.id2 
    , Coalesce (m1.id2, m2.id2,null) id2_1, m0.rn2
    --, Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2)   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
    , Case when m0.id1= Coalesce (m1.id2, m2.id2,null)   Then 1 Else 0 END  as cnt2
    , Case when m0.id2=Coalesce (m3.id1, m2.id1,null)  Then 1 Else 0 END   as cnt3
    from mycte m0 
    Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
    Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
    Left Join mycte m3 On  m0.name =m3.name and m0.rn1=m3.rn1-1
    
    )
    
    
    Select Rec_id,name, id1,id2
    --,cnt, cnt2, cnt3  
    from mycte1
    WHERE cnt2*cnt3=0
    
    Order by Rec_id
    

    Tuesday, April 15, 2014 5:07 PM
  • Li,

    Based on the latest query, I see some rows which are not required to display in the output.
    These are the rows which are not required..
    Rec_Id = 4, 11, 15,18,

    Also need to display Rec_Id=17 in the ouptput which we are missing with latest query. Can I get updated SQL to get correct output?

    Thanks in advance,
    RH


    sql

    Tuesday, April 15, 2014 6:42 PM
  • Hi Li,

    I am getting all scenarios with below SQL but except for H-Group. In H-Group Instead of displaying Rec_id's=28 and 29 it's displaying Rec_Id's =28 and 30. Please I need your help. With your help only I Can go forward.

    ;with mycte as
    (select Rec_Id,name, id1, id2 
    ,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1
    ,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
     
    from sample )
    , mycte1 as(
    select m0.Rec_id,m0.name, m0.id1
    , Coalesce (m3.id1, m2.id1,'')  as id1_3, m0.id2
    , Coalesce (m1.id2, m2.id2,'') id2_1, m0.rn2
    , Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'')   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
    from mycte m0 Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
    Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
    Left Join mycte m3 On  m0.name =m3.name and m0.rn1=m3.rn1-1
    )
    Select Rec_id,name, id1,id2 from mycte1
    WHERE  rn2=1 AND cnt>0
    UNION
    Select Rec_id,name, id1,id2 from mycte1
    WHERE id1_3<>id2

    Order by Rec_id

    Thanks,

    RH


    sql

    Wednesday, April 16, 2014 11:13 AM
  • USING CTE

    ;WITH CTE 
    AS(
    SELECT Rec_Id , Name,  ID1,   ID2, 
       ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Rec_Id DESC) AS RN 
    FROM Table_Name
    )
    SELECT Rec_Id , Name,  ID1,   ID2 
    FROM CTE 
    WHERE RN = 1

    USING Sub-Query

    SELECT Rec_Id , Name,  ID1,   ID2 
    FROM ( SELECT Rec_Id , Name,  ID1,   ID2,          
           ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Rec_Id DESC) AS RN 
    FROM Table_Name)A
    WHERE RN = 1

    Wednesday, April 16, 2014 11:21 AM
  • I am still struggle with the issue.Any luck Li?

    sql

    Wednesday, April 16, 2014 9:27 PM
  • Did you read this sentence?

    When you cut a loop, you will have two parties involved. 

    This is what I feel that your logic does not fit that well with your final additional requirement. If you want the final two pieces returned, you should review the early requirement whether they are valid or not.

    Thanks.

    Wednesday, April 16, 2014 9:52 PM
  • Hi Li,

    I have tried something like this and got the results for final two pieces along with previous scenarios. Can you please advise whether my approach is correct or not?

    Code:

    WITH CTE1 AS (
    SELECT REC_ID,NAME, ID1, ID2 
    ,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY REC_ID) AS RN1
    ,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY REC_ID DESC) AS RN2
    FROM SAMPLE
    )
    , CTE2 AS(
    SELECT m0.Rec_id,m0.NAME, m0.ID1, m3.ID1 AS M3ID1, m2.ID1 AS M2ID1,m1.ID2 AS M1ID2, m2.ID2 AS M2ID2
    , COALESCE (m3.ID1, m2.ID1,NULL)  AS ID1_3, m0.ID2
    , COALESCE (m1.ID2, m2.ID2,NULL) ID2_1, m0.RN2
    , SUM(CASE WHEN m0.ID1=COALESCE (m1.ID2, m2.ID2)   THEN 0 ELSE 1 END) OVER(PARTITION BY m0.NAME)  AS CNT1
    , CASE WHEN m0.ID1= COALESCE (m1.ID2, m2.ID2,NULL)   THEN 1 ELSE 0 END  AS CNT2
    , CASE WHEN m0.ID2=COALESCE (m3.ID1, m2.ID1,NULL)  THEN 1 ELSE 0 END   AS CNT3
    FROM CTE1 m0
    LEFT JOIN CTE1 m1 ON  m0.NAME =m1.NAME AND m0.RN1=m1.RN1+1
    LEFT JOIN CTE1 m2 ON  m0.NAME =m2.NAME AND m0.RN2=m2.RN1
    LEFT JOIN CTE1 m3 ON  m0.NAME =m3.NAME AND m0.RN1=m3.RN1-1
    )
    ,CTE3 AS (
    SELECT * FROM (
    SELECT REC_ID,NAME, ID1,ID2,COUNT(*) OVER (PARTITION BY NAME,ID2) AS RNCNT
    FROM CTE2
    WHERE CNT2*CNT3=0
    ) A WHERE A.RNCNT>1
    )
    ,CTE4 AS (
    SELECT REC_ID,NAME, ID1,ID2 FROM CTE2
    WHERE  RN2=1 AND CNT1>0
    UNION
    SELECT REC_ID,NAME, ID1,ID2 FROM CTE2
    WHERE ID1_3<>ID2
    )
    ,CTE5 AS (
    SELECT DISTINCT C4.REC_ID,C3.REC_ID AS C3REC_ID,C4.NAME, C4.ID1,C4.ID2,C3.ID1 AS C3ID1,C3.ID2 AS C3ID2
       ,CASE WHEN C4.ID1=COALESCE (C3.ID1, C4.ID1,NULL)  THEN 1 ELSE 0 END   AS CNT4
       ,CASE WHEN C4.ID2=COALESCE (C3.ID2, C4.ID2,NULL)  THEN 1 ELSE 0 END   AS CNT5
    FROM CTE4 AS C4
    LEFT JOIN CTE3 AS C3 ON C3.NAME=C4.NAME
    )
    SELECT C5.REC_ID,C5.NAME,C5.ID1,C5.ID2 FROM CTE5 C5
    WHERE C5.CNT4=1
    UNION
    SELECT C3REC_ID,NAME, C3ID1,C3ID2 FROM CTE5 C5
    WHERE C5.CNT4=0 AND C5.CNT5=0

    Thanks,

    RH


    sql

    Thursday, April 17, 2014 9:19 PM