CODEWITHSUNDEEP
sqlsql-servert-sql
bitsmuggler
bitsmuggler

Reputation: 1729

T-SQL: "Compress" rows with equal values to one

Is there a chance to show only one row if there are any rows with same values?

I've the following scenario:

ID | Column A | Column B | Column C

1 | 2 | 'test' | 5

2 | 3 | 'test'| 6

3 | 2 | 'test'| 5

In this scenario I want only show the following resultset:

ID | Column A | Column B | Column C

1 | 2 | 'test' | 5

2 | 3 | 'test'| 6

Thanks for your help.

Regards, pro

Upvotes: 0

Views: 715

Answers (4)

vmvadivel
vmvadivel

Reputation: 1067

CREATE TABLE #test
(
    ID      TINYINT     NOT NULL,
    colA    TINYINT     NOT NULL,
    colB    VARCHAR(10) NOT NULL,
    colC    TINYINT     NOT NULL
);

INSERT INTO #test VALUES (1,2, 'test', 5);
INSERT INTO #test VALUES (2,3, 'test', 6);
INSERT INTO #test VALUES (3,2, 'test', 5);

SELECT 
    ID,
    ColA,
    ColB,
    ColC
FROM 
(   
    SELECT 
        ID,
        ColA,
        ColB,
        ColC,
        ROW_NUMBER() OVER(PARTITION BY ColA ORDER BY ColA DESC) AS RowNum
      FROM #test
) AS WorkTable
WHERE RowNum = 1

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

select id, column1,column2,colum3 from
(
    select *, row_number() over (partition by column1,column2,colum3 order by id) as sno 
    from table
) as t
where sno=1

Upvotes: 0

praveen
praveen

Reputation: 12271

Try this :

With cte As
( Select  * , row_number() over (partition by ColumnA, ColumnB,ColumnC
 order by ID ) as myrownumber from myTable
)
Select * from cte where myrownumber=1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Your rows are not exact duplicates, because of the id column. If you don't care which value of the id appears, you can do what you want as:

select max(id) as id, ColumnA, ColumnB, ColumnC
from t
group by ColumnA, ColumnB, ColumnC

If you don't need the id at all, this is simpler:

select distinct ColumnA, ColumnB, ColumnC
from t

Upvotes: 6

Related Questions