locked
How to group by and filter data based on condition RRS feed

  • Question

  • User-932240137 posted

    ID        type    seq

    1234    o          1

    1234    v          1

    1234     r          1  > i need this row

    ID       type     seq

    1235     o       1

    1235     r        1  > i need this row

    ID      type     seq

    1236    o        1

    1236    v         1  //Nothing to display on this condition

    I have following field on my table ID, Type and Seq

    if I have rowset 0,V Then display nothing

    if rowset is R, V  then display R

    if rowset is O, R then display R

    Any help would be appreciated..

    I WROTE AS :

    SELECT C.SEQ, C.Type
    FROM TableA C GROUP BY C.SEQ, C.Type
    HAVING (C.Type NOT IN ('O', 'V'));

    but this does not cover all the scenarios.

    Thanks guys

    Friday, July 15, 2016 1:36 AM

Answers

  • User475983607 posted

    Boracay

    1. i need to ignore case based on the following condition   if O AND V - ignore both rows   if O AND R - Take a row with R   if R AND V - ignore both rows

    2. there is always at least two rows in a group and Output will be either O/R

    How is it possible to have an O given requirement 1?

    As far as I can, tell Chris's solution works each of your requirements.  What requirement fails given Chris's solution?

    IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
    	DROP TABLE #TableA 
    
    
    CREATE TABLE #TableA (
    	ID		INT,
    	[Type]	CHAR,
    	Seq		INT
    )
    
    INSERT INTO #TableA (ID, [Type], Seq) 
    VALUES	(1234, 'O', 1),
    		(1234, 'V', 1),
    		(1234, 'O', 2),
    		(1234, 'R', 2),
    		(1234, 'O', 3),
    		(1234, 'V', 3)
    
    SELECT C.ID,C.SEQ, C.Type
    FROM #TableA C GROUP BY C.ID,C.SEQ, C.Type
    HAVING (C.Type NOT IN ('o', 'v'));

    Result

    ID          SEQ         Type
    ----------- ----------- ----
    1234        2           R

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 17, 2016 5:20 PM

All replies

  • User-1404113929 posted

    hi,

    can you try this code

    SELECT C.SEQ, C.Type
    FROM TableA 
    where (C.Type NOT IN ('O', 'V')) GROUP BY C.SEQ, C.Type

    Friday, July 15, 2016 4:55 AM
  • User-2057865890 posted

    Hi Boracay,

    Group by ID

    SELECT C.ID,C.SEQ, C.Type
    FROM TableA C GROUP BY C.ID,C.SEQ, C.Type
    HAVING (C.Type NOT IN ('o', 'v'));

    Result:

    Best Regards,

    Chris

    Friday, July 15, 2016 7:43 AM
  • User-932240137 posted

    Thanks Chris for the reply.

    ID	type  seq
    1234	o	1
    1234	r	1  > i need this row  IF o and R comes with same SEQ and ID
    
    ID	type  seq
    1234	v	2
    1234	r	2  > //Nothing to display on this condition  because V  voids r
    ID	type  seq
    1234	o	3
    1234	v	3  //Nothing to display on this condition  because V voids O
    
    
    i have a large set  of  data group by SEQ and ID.
    
    my problem is how do i  loop through the group to look for the following pattern
    
    if Type and Sequence comes as 
    a) o, v - disregard
    b) O, R - take the row with R
    c) R, V - disregard
    select x. id, x.type, x.seq
    from
    (
    Select Unique p.id, p.type, p.seq  from tableA p 
    ) x
    group by x.seq, x.type, x.id
    having x.type not in ('O','V')
     -- This does not help because some data comes with type ('O','R') with same sequence and same id and this situation --i need to take row with 'R'
    order by x.seq asc;
    
    I appreciate your help and suggestion.
    Thanks,

    Saturday, July 16, 2016 10:56 PM
  • User-932240137 posted

    Thanks Chris,

    I am providing you more information here

    * Rows separated into Groups by ID and SEQ

    * Types are always : O, R, V (All Upper-case)

    * i need to ignore case based on the following condition   if O AND V - ignore both rows   if O AND R - Take a row with R   if R AND V - ignore both rows

    * there is always at least two rows in a group and Output will be either O/R

    * do you need to select EXACTLY the pairs (id, seq) that have EXACTLY o and r - and then select the row with r -YES

    * Can there be more than two rows in one group?- Yes (What if you have one with o, one with r and one with v?) - I only need one with either O/R

    * Can there be duplicates?- Yes . What if you have a pair (id, seq) with three rows, with types o, o and r? Or with o, r, r?   if O O R - I need with R  or if O RR - i need with R

    Sunday, July 17, 2016 5:41 AM
  • User475983607 posted

    Boracay

    1. i need to ignore case based on the following condition   if O AND V - ignore both rows   if O AND R - Take a row with R   if R AND V - ignore both rows

    2. there is always at least two rows in a group and Output will be either O/R

    How is it possible to have an O given requirement 1?

    As far as I can, tell Chris's solution works each of your requirements.  What requirement fails given Chris's solution?

    IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
    	DROP TABLE #TableA 
    
    
    CREATE TABLE #TableA (
    	ID		INT,
    	[Type]	CHAR,
    	Seq		INT
    )
    
    INSERT INTO #TableA (ID, [Type], Seq) 
    VALUES	(1234, 'O', 1),
    		(1234, 'V', 1),
    		(1234, 'O', 2),
    		(1234, 'R', 2),
    		(1234, 'O', 3),
    		(1234, 'V', 3)
    
    SELECT C.ID,C.SEQ, C.Type
    FROM #TableA C GROUP BY C.ID,C.SEQ, C.Type
    HAVING (C.Type NOT IN ('o', 'v'));

    Result

    ID          SEQ         Type
    ----------- ----------- ----
    1234        2           R

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 17, 2016 5:20 PM