user7002207 user7002207 - 1 month ago 13
SQL Question

Select rows with same id but only one row is needed

I want to get the only row having a value of yes for a particular username

If both rows have yes for that particular username or both have no, then it should not appear in output.

Below is example sample and output. How it can be done using sql query?

+----------+-----+-------+
| username | id | col2 |
+----------+-----+-------+
| a | 1 | yes |
| a | 2 | yes |
| b | 3 | yes |
| b | 4 | no |
| c | 5 | no |
| c | 6 | no |
+----------+----+--------+


output

+---------+---+------+
| username|id | col2 |
+---------+---+------+
| b |3 | yes |
+---------+---+------+


Any help is greatly appreciated

Answer

For this exercise you don't need joins or subqueries; all you need is GROUP BY and HAVING.

select username, 'yes' as col2
from   table_name
group by username
having min(col2) != max(col2)

Edit (to address the OP's restated question):

select username, min(id) keep (dense_rank last order by col2) as id, 'yes' as col2
from   table_name   [....   the rest as above]
Comments