2

my columns in a table like this

column1  column2
100       1
100       1
101       2
101       3
102       4
102       5
103       6
104       7
104       7

and I want the output like this

column1 column2
101       2
101       3
102       4
102       5
103       6
Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276
user980191
  • 61
  • 1
  • 4

3 Answers3

1
declare @T table
(
  c1 int,
  c2 int
)

insert into @T values
(100,       1),
(100,       1),
(101,       2),
(101,       3),
(102,       4),
(102,       5),
(103,       6),
(104,       7),
(104,       7)

;with C as
(
  select count(*) over(partition by C1, C2) as D
  from @T
)
delete from C
where D > 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    I do not agree with your naming convention: `C` would be a more natural choice of a name for `COUNT(*)`, than `D`, and, to avoid possible confusion, the `C` CTE could be named `E`! Just joking :) This is a(nother) great example of using a CTE actually! – Andriy M Jan 28 '12 at 16:15
  • 1
    @AndriyM - Ok, I will work a bit in names :). I guess `D` in this case could be short for `DuplicateCount`. `C` is actually an excellent name when it is a derived table of the table `T` :). – Mikael Eriksson Jan 28 '12 at 16:39
0

If you just want a query to return the rows without repeats, you want:

SELECT DISTINCT column1, column2
FROM MyTable

If you actually want to delete the rows, then follow @Ahamed's link to a previous SO answer on deleting duplicate rows

Community
  • 1
  • 1
perfectionist
  • 4,256
  • 1
  • 23
  • 34
0

Apparently you only want rows whose column2's value is between 2 and 6

Delete from your_table
Where column2<2 or column2>6

Now you will be left only with the rows you want, you can just select all now:

Select column1, column2
From yourtable
Icarus
  • 63,293
  • 14
  • 100
  • 115