MySQL take rows and override ones without user_id

Question

I have table like this one:

I would like to all rows, but if there is user_id 5 if this case, override other rows which have no user_id.

I tried both with MAX(user_id) and GROUP BY country_name, but it still returns, wrong results.

Final result I'm expecting:

I think this is what you're looking for: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Yannici, Jun 22 '16 at 08:18

score 1 · Accepted Answer · answered Jun 22 '16 at 08:25

Try this;)

select t1.*
from yourtable t1
inner join (
    select max(user_id) as user_id, country_name from yourtable group by country_name
) t2 on t1.country_name = t2.country_name and t1.user_id = t2.user_id

This is just a solution based on your sample data. If you have a variety of user_id, it should be more different.

score 1 · Answer 2 · edited May 23 '17 at 11:52

As of SQL Select only rows with Max Value on a Column you can easily get rows with max value on a column by using both MAX(column) and GROUP BY other_column in one statement.

But if you want to select other columns too, you have to this in a subquery like in the following example:

SELECT a.*
FROM YourTable a
INNER JOIN (
    SELECT country_name, MAX(user_id) user_id
    FROM YourTable
    GROUP BY country_name
) b ON a.country_name = b.country_name AND a.user_id = b.user_id

MySQL take rows and override ones without user_id

2 Answers2