locked
Query to find out distinct values in a column RRS feed

  • Question

  • User-273049309 posted

    Hi,

     I am looking for a query. I have a table as below. So I need to find out the distinct category for a combination of Col A,B,C. So first 3 rows in below table has same Col A,B,C and different Item (which can be) and all these items has to be belong to a Single category. But below category is distinct for the combination of Col A,B,C hence need to update Error column for all those rows as "has to be from single category". Can someone help on this please. Thank You.

    Col A Col B Col C Item Category Error
    1 2 3 Item1 Category1 has to be from single category
    1 2 3 Item2 Category1 has to be from single category
    1 2 3 Item3 Category2 has to be from single category
    10 11 12 Item4 Category5  
    20 21 22 Item4 Category5  
    Tuesday, January 26, 2016 8:39 PM

Answers

  • User-808054615 posted

    Hi,

    Praveen, I do not know if I understand the question correctly, but try something like this:

    with CTE_Count as
    (
        select
            ColA,
            ColB,
            ColC
        from MyTable
        group by
            ColA,
            ColB,
            ColC
        having
            COUNT(distinct Category) > 1
    )
    
    update t
    set t.Error = 'has to be from single category'
    from CTE_Count as c
    inner join MyTable as t
        on t.ColA = c.ColA and
           t.ColB = c.ColB and
           t.ColC = c.ColC

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 26, 2016 10:49 PM
  • User475983607 posted

    Temp table verion

    IF OBJECT_ID('tempdb..#Category') IS NOT NULL
    	DROP TABLE #Category
    
    IF OBJECT_ID('tempdb..#ErrorCols') IS NOT NULL
    	DROP TABLE #ErrorCols
    
    
    CREATE TABLE #Category(
    	ColA		INT,
    	ColB		INT,
    	ColC		INT,
    	Item		VARCHAR(20),
    	Category	VARCHAR(20),
    	Error		VARCHAR(256)
    )
    
    INSERT INTO #Category(ColA, ColB, ColC, Item, Category)
    VALUES	(1, 2, 3,		'Item1', 'Category1'),
    		(1, 2, 3,		'Item2', 'Category1'),
    		(1, 2, 3,		'Item3', 'Category2'),
    		(10, 11, 12,	'Item1', 'Category1'),
    		(20, 21, 22,	'Item1', 'Category1')
    
    SELECT ColA, ColB, ColC, COUNT(Category) as [Count]
    INTO #ErrorCols
    FROM #Category
    GROUP BY ColA, ColB, ColC
    HAVING COUNT(Category) > 1
    
    UPDATE c
    SET c.Error = 'has to be from single category'
    FROM #Category AS c
    	INNER JOIN #ErrorCols ec ON c.ColA = ec.ColA
    							AND c.ColB = ec.ColB
    							AND c.ColC = ec.ColC
    
    
    SELECT * 
    FROM #Category

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 26, 2016 10:56 PM

All replies

  • User475983607 posted

    Add a unique constraint that includes ColA, ColB, ColC, and Category.

    https://msdn.microsoft.com/en-us/library/ms190024.aspx

    Tuesday, January 26, 2016 10:20 PM
  • User-273049309 posted

    Hi Mgebhard,

    Thanks for the Reply but can I get this achieve through sql query? I also need to update the Error column with message above which doesn't meet the constraint above.

    Tuesday, January 26, 2016 10:25 PM
  • User475983607 posted

    Hi Mgebhard,

    Thanks for the Reply but can I get this achieve through sql query? I also need to update the Error column with message above which doesn't meet the constraint above.

    The requirements seem to conflict. Why is row 3 an error?

    How about posting your current script.

    Tuesday, January 26, 2016 10:42 PM
  • User-808054615 posted

    Hi,

    Praveen, I do not know if I understand the question correctly, but try something like this:

    with CTE_Count as
    (
        select
            ColA,
            ColB,
            ColC
        from MyTable
        group by
            ColA,
            ColB,
            ColC
        having
            COUNT(distinct Category) > 1
    )
    
    update t
    set t.Error = 'has to be from single category'
    from CTE_Count as c
    inner join MyTable as t
        on t.ColA = c.ColA and
           t.ColB = c.ColB and
           t.ColC = c.ColC

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 26, 2016 10:49 PM
  • User475983607 posted

    Temp table verion

    IF OBJECT_ID('tempdb..#Category') IS NOT NULL
    	DROP TABLE #Category
    
    IF OBJECT_ID('tempdb..#ErrorCols') IS NOT NULL
    	DROP TABLE #ErrorCols
    
    
    CREATE TABLE #Category(
    	ColA		INT,
    	ColB		INT,
    	ColC		INT,
    	Item		VARCHAR(20),
    	Category	VARCHAR(20),
    	Error		VARCHAR(256)
    )
    
    INSERT INTO #Category(ColA, ColB, ColC, Item, Category)
    VALUES	(1, 2, 3,		'Item1', 'Category1'),
    		(1, 2, 3,		'Item2', 'Category1'),
    		(1, 2, 3,		'Item3', 'Category2'),
    		(10, 11, 12,	'Item1', 'Category1'),
    		(20, 21, 22,	'Item1', 'Category1')
    
    SELECT ColA, ColB, ColC, COUNT(Category) as [Count]
    INTO #ErrorCols
    FROM #Category
    GROUP BY ColA, ColB, ColC
    HAVING COUNT(Category) > 1
    
    UPDATE c
    SET c.Error = 'has to be from single category'
    FROM #Category AS c
    	INNER JOIN #ErrorCols ec ON c.ColA = ec.ColA
    							AND c.ColB = ec.ColB
    							AND c.ColC = ec.ColC
    
    
    SELECT * 
    FROM #Category

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 26, 2016 10:56 PM
  • User-273049309 posted

    Thanks for replying. Basically  the combination shall have only one category. If even on row is having different category then all the records with that combination has to be updated saying items from same category are accepted. So user need to correct 3 rd row as Category1 or first 2 rows as Category 2.

    Wednesday, January 27, 2016 7:11 PM
  • User-273049309 posted

    I will try this approach. It's close to what I am looking for. Thanks a lot for your help on this. Will let you know the result.

    Wednesday, January 27, 2016 7:12 PM
  • User-273049309 posted

    Thanks mgebhard for sending it in Detailed. Thanks for your help.

    Wednesday, January 27, 2016 9:59 PM