Skip to content
Advertisement

Postgresql select distinct Column A based on certain conditions on Column B

I have a table with data:

+--------+---------+
| userid | status  |
+--------+---------+
| user_1 | success |
| user_2 | fail    |
| user_2 | success |
| user_3 | fail    |
| user_3 | fail    |
+--------+---------+

I would like my query output to be distinct on userid but with condition that between fail and success values in status column. I would like to choose success instead (if both fail as in user_3, choose fail then). The table below shows the output that I would like to have as my result:

+--------+---------+
| userid | status  |
+--------+---------+
| user_1 | success |
| user_2 | success |
| user_3 | fail    |
+--------+---------+

Any efficient query would be nice as well. Thanks!

Advertisement

Answer

Here is a pretty efficient way to get the results you need.

SELECT userid, MAX(status)
FROM table1
GROUP BY userid

The MAX() function will work for strings as well. Since, “success” > “fail”, if a userid has 1 row of “success” and 1 row of “fail”, the maximum value is “success”

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement