Mysql Group by showing multiple rows

Question

Database structure

Table 'Org_f_a_list'

  id    org_id    user_id    date_added
  1          1          1      2017-01-01 05:05:05

Table 'Users'

  id      username      last_login
   1      Testuser     2017-01-01 05:05:05

Table 'Users_pa'

  id      user_id        summoner_id       rank_solo
   1            1                  1               15
   2            1                  2               17

My current query

select max(rank_solo) as rank,last_login,username,o.user_id,date_added 
      from org_f_a_list o
      join users u on o.user_id = u.id 
      join users_pa as p on u.id = p.user_id
      where org_id = :org 
      group by u.id,rank_solo,date_added
      order by rank desc

What I want the result to be

 user_id      user_name     rank    date_added    last_login
       1      Testuser       17           date          date

My current result

   user_id     user_name    rank      date_added    last_login
         1     Testuser       15            date          date
         1     Testuser       17            date          date

For some reason the group by u.id is not doing anything and I still pull both rows and not just the max rank

Edit : That fixed it. Thanks guys!

Gouda Elalfy · Accepted Answer · 2016-01-13 18:46:38Z

1

remove rank_solo from group by

select max(rank_solo) as rank,last_login,username,o.user_id,date_added 
from org_f_a_list o
join users u on o.user_id = u.id 
join users_pa as p on u.id = p.user_id
where org_id = :org 
group by u.id,date_added
order by rank desc

answered Jan 13, 2016 at 18:46

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

idiotbox · Accepted Answer · 2016-01-13 18:50:27Z

0

Rank Should not be part of group by as it is causing forming of two different groups

answered Jan 13, 2016 at 18:50

idiotbox

698 bronze badges

Comments

Meta_data · Accepted Answer · 2016-01-13 18:54:33Z

0

You can Remove the Rank_solo from the Group By Like this:

  select max(rank_solo) as rank,last_login,username,o.user_id,date_added 
  from org_f_a_list o
  join users u on o.user_id = u.id 
  join users_pa as p on u.id = p.user_id
  where org_id = :org 
  group by u.id,date_added
  order by rank desc

You can Use Having with the same Query

  select max(rank_solo) as rank,last_login,username,o.user_id,date_added 
  from org_f_a_list o
  join users u on o.user_id = u.id 
  join users_pa as p on u.id = p.user_id
  where org_id = :org 
  group by u.id,date_added
  Having rank_solo = max(ranl_solo)
  order by rank desc

answered Jan 13, 2016 at 18:54

Meta_data

6081 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

Mysql Group by showing multiple rows

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related