asked
How to Ignore duplicate column?
Hello, I need to ignore the rows that contain duplicate values in columns. I do not want to delete any rows in my table, only need to ignore them in my report. A1, Z1, TESTA A2, Z2, TESTB A3, Z3, TESTA A4, Z4, TESTF So I need to ignore rows 3, and keep rows 1,2,4 only. The output will become like this: A1, Z1, TESTA A2, Z2, TESTB A4, Z4, TESTF Any help is appreciated. Thanks.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
You can use SELECT DISTINCT for this.
Sorry, you're quite right. I think I decided I knew what you were asking before I had read your question properly!
So this depends on knowing which columns have the duplicates in.. declare @YourTable table (col1 varchar(10), col2 varchar(10), col3 varchar(10)) insert into @YourTable select 'A1', 'Z1', 'TESTA' insert into @YourTable select 'A2', 'Z2', 'TESTB' insert into @YourTable select 'A3', 'Z3', 'TESTA' insert into @YourTable select 'A4', 'Z4', 'TESTF' select min(col1), min(col2), col3 from @YourTable group by col3 returns col3 ---------- ---------- ---------- A1 Z1 TESTA A2 Z2 TESTB A4 Z4 TESTF
I did something like this for your reference; ;with yourtable1(A, Z, T) as ( values ('A1', 'Z1', 'TESTA'), ('A2', 'Z2', 'TESTB'), ('A3', 'Z3', 'TESTA'), ('A4', 'Z4', 'TESTF') ), yourtable2 as ( SELECT A, Z,T, row_number() OVER (PARTITION BY T ORDER BY A) as RN FROM yourtable1 ) SELECT A,Z,T FROM yourtable2 WHERE RN = 1
18 People are following this question.
