Mysql – How to write a query to get concatenated names of riders with the same weight

MySQLmysql-8.0

I want to retrieve a table with all Riders of the same Weight from a table named Rider, which is defined in this way:

CREATE TABLE Rider(
    name VARCHAR(30),
    weight REAL,
    PRIMARY KEY(name)
) ENGINE=innodb;

The data in it is:

Alan  55
Ana   55
Anne  50
Bill  65
David 60
Julia 55
Peter 65
Stan  60

I want a result like this:

50 Anne
55 Alan Ana Julia
60 David Stan
65 Bill Peter

I can't figure out how to do it. I can only manage to sort them all by weight with:

  SELECT weight, name 
    FROM Rider 
GROUP BY weight;

All help is greatly appreciated!

Best Answer

You're looking for GROUP_CONCAT:

SELECT weight, GROUP_CONCAT(name SEPARATOR ' ') as name_list
FROM Rider
GROUP BY weight

Related Solutions

Mysql – How to get a user’s friends’ names

If you want to get the names of the friends for a user perhaps try something like so:

SELECT u.firstname, u.surname 
FROM friend f, user u
WHERE (f.Person1 = 1 OR f.Person2 = 1)

This should get a back a list of names in your user table (providing that is what you have named your friends in the user table) that are friends with userID 1

I'm guessing your friend table contains the foreign key IDs of the friends making up the partnership?

Mysql – How to find the same salaries and department names

This should work:

select e1.* 
  from employees e1 inner join (
    select count(employee_id) as CNT, department_id, salary 
    from employees group by departmant_id, salary) t1 
  on e1.department_id=t1.department_id and e1.salary=t1.salary 
  where t1.CNT>1

The problem with your own query is twofold:

the return value of (select...from ..e2..) is from the very nature of your problem not unique, so the "=" relationship should either throw an error or return whichever is the first result (I'm not familiar enough with MySQL to know which applies), which doesn't have to be the one you're looking for.
filtering via "e1.employee_id =" will only ever be true for the record itself, but NOT for another record that matches your criteria

If you insist on using a filter:

SELECT * FROM employees e1
WHERE (e1.department_id,e1.salary) IN 
 (SELECT e2.department_id,e2.salary
  FROM employees e2
  WHERE e2.employee_id <> e1.employee_id)

Best Answer

Related Solutions

Mysql – How to get a user’s friends’ names

Mysql – How to find the same salaries and department names

Related Question