TSQL, Remove rows based upon Row-Index

Question

My Table is like this.....

**AttName**     **Title**        **Count_Index**

Red                Boys              1
Red                Girls             2
Green              Boys              1
Blue               Boys              1

I only Want to return...

Red  Boys        1
Red  Girls       2

Thats because I have Red with two entries, I want to skip/remove all the ROW(s) if their Count is ONLY 1. In other words I am only interested in rows if their count goes above "1".

Is `Count` a field, or a calculated count using the aggregate function? — , May 02 '13 at 22:34
Because you said : I have this , I want that. You forgot the part where you try something and tell us what you tried. — phadaphunk, May 02 '13 at 22:53
I just did not feel like POSTING my entire stored procedure, I tried to simplify it with some dummy data only so everyone can understand it. — highwingers, May 02 '13 at 22:55

score 2 · Accepted Answer · answered May 02 '13 at 22:41

Try

SELECT * 
  FROM table1
 WHERE AttName IN (SELECT AttName FROM table1 GROUP BY AttName HAVING COUNT(*) > 1)

SQLFiddle

Output

| ATTNAME | TITLE | COUNT_INDEX |
---------------------------------
|     Red |  Boys |           1 |
|     Red | Girls |           2 |

AaronLS · Answer 2 · 2013-05-02T22:52:15.050

0

Ok, this is tested. I like using windowing functions when looking for things like duplicates. Particularly because the avoids doing a subselect in a where clause, and from the same table twice. Instead all the needed columns are already pulled in the subselect. Although windowing function can be expensive sometimes.

Select *, ROW_NUMBER() over (Partition by AttrName Order By AttrName) --probably better to order by whatever the primary key is for consistent results, esepcially if you plan to use this in a delete statement
From (
  SELECT AttName, title, COUNT(AttrName) over (partition by AttrName) as cnt
  FROM yourtable
) as counted
Where counted.cnt > 1

edited May 02 '13 at 22:52

answered May 02 '13 at 22:38

AaronLS

37,329
20
143
202

Why the `Count(*)` and not `Count(AttName)` since it's the only count that matters... – phadaphunk May 02 '13 at 22:40
1

And what about the fact that he wants the columns `AttName`, `Title` and `Count_indes` in the results ? – phadaphunk May 02 '13 at 22:41
1

@PhaDaPhunk The difference is whether you want nulls counted or not. Updated query based on feedback, thanks. See http://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct – AaronLS May 02 '13 at 22:54

TSQL, Remove rows based upon Row-Index

2 Answers2