CODEWITHSUNDEEP
sql-server
Kunal
Kunal

Reputation: 45

sql query to list rows with same column values and then delete a specific row

I am new to sql and I'm using MS sql management server for examining a database. I need to write a sql query to list rows with same column values and then delete a specific row. Eg:

+------+------+
|pid   |code  |
+------+------+
|1     |A     |
+------+------+
|2     |A     |
+------+------+
|3     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |B     |
+------+------+

For the above table, first I need to group rows with same 'pid'. Then within any group, if 'A' and 'B' are both present, I need to delete the rows with 'code' entry 'B'. So from the above table I need to delete last 2 rows. Any help would be very much appreciated.

Thanks

Upvotes: 1

Views: 159

Answers (1)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

Try like this;

select * from table1 where pid IN (
select pid from table1 group by pid 
having count(distinct code) > 1 and count(CASE WHEN code = 'A' THEN 1 END) > 0)
and code = 'B'

Demo

Upvotes: 2

Related Questions