Reputation: 1026
again I met some problem with the SQL and couldn't brain it. Based on below situation, we noted that user_id repeated twice and I wanted to distinct/group it up to 1 row but there is a case that I would consider anonymous column and the logic would be to return the row of anonymous that equal to 1 which I believe distinct could have just take them randomly? Anyone can enlighten me? Appreciate for response. Thank you.
__________________________
|user_id|anonymous|amount|
--------------------------
| 1234| 1| 100|
| 222| 0| 90|
| 1234| 0| 88|
==========================
**Final answer** should look like this.
__________________________
|user_id|anonymous|amount|
--------------------------
| 1234| 1| 100|
| 222| 0| 90|
==========================
Upvotes: 1
Views: 41
Reputation: 204924
select t.* from your_table t
join
(
select user_id, max(anonymous) as m_anon
from your_table
group by user_id
) x on x.user_id = t.user_id and x.m_anon = t.anonymous
Upvotes: 4