CODEWITHSUNDEEP
mysqlsqlselect
Shawn31313
Shawn31313

Reputation: 6052

Reducing a results by combining them

I have a table with the following info that is generated through a few joins with other tables without the unique ID showing:

id | username | blocked | online
 2 | The_Boss |    0    |   0 
 2 | The_Boss |    0    |   0 
 3 | The_Dude |    1    |   1 
 3 | The_Dude |    0    |   0

That i would like to give the following results:

id | username | blocked | online
 2 | The_Boss |    0    |   0 
 3 | The_Dude |    1    |   1

id is a userid that matched the username. Essentially what I need to do is generate a list of online/offline users given that the user has multiple different online records depending on different sections of a page that he is online at. Regardless, if the user is online at any section I would like the row where online=1 to take precedence of offline = 0 but each userid should become unique. So in a bigger scale it would look like:

id | username | blocked | online
 2 | The_Boss |    0    |   0 
 2 | The_Boss |    0    |   0 
 2 | The_Boss |    0    |   0 
 3 | The_Dude |    1    |   1 
 3 | The_Dude |    0    |   0 
 3 | The_Dude |    1    |   1 
 4 | The_Kind |    0    |   0 
 4 | The_Kind |    0    |   0 
 4 | The_Kind |    0    |   0
 4 | The_Kind |    0    |   0
 5 | The_Mann |    0    |   0
 5 | The_Mann |    0    |   1

Returns:

 id | username | blocked | online
 2 | The_Boss |    0    |   0 
 3 | The_Dude |    1    |   1 
 4 | The_Kind |    0    |   0
 5 | The_Mann |    0    |   1

Upvotes: 0

Views: 22

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

seems you need the distinct values 

SELECT   distinct  username, blocked, online
from your_table 
order by username, blocked  desc, online

Upvotes: 0

Mureinik
Mureinik

Reputation: 311518

You could group the results by the user id and name, and take the maximum statuses:

SELECT   id, username, MAX(blocked), MAX(online)
FROM     mytable
GROUP BY id, username

Upvotes: 1

Related Questions