Eric T
Eric T

Reputation: 1026

SQL based on IF case (read details)

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

Answers (1)

juergen d
juergen d

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

Related Questions