1

I have a table my_table like (and the NAME column values are unique):

ID   NAME   RANK   AGE
1    John   4      21
2    Sam    1      43
3    Tom    3      57
4    Bob    2      39
5    Dave   5      25

I want to run a single query where for every row I select all the attributes plus another column that is a list of other names sorted by age DESC and NOT itself with a LIMIT (let's say 3), with the results something like:

NAME   RANK   AGE   SOME_LIST
John   4      21    Tom,Sam,Bob
Sam    1      43    Tom,Bob,Dave
Tom    3      57    Sam,Bob,Dave
Bob    2      39    Tom,Sam,Dave
Dave   5      25    Tom,Sam,Bob

So the query to just get SOME_LIST for John would be something like:

SELECT name FROM my_table WHERE name <> 'John' ORDER BY age DESC LIMIT 3;

and the result would look like:

NAME
Tom
Sam
Bob

I'm not sure 1) how to get these results into a single column comma-separated and 2) how to run this query for every row in my_table.

UPDATE:

Getting closer with this query:

SELECT *, (SELECT GROUP_CONCAT(name) FROM my_table t1 WHERE t1.name <> t2.name ORDER BY age DESC LIMIT 3) AS some_list FROM my_table t2;

But the some_list column does not respect the age desc order by and also does not respect the limit. Only the <> is accepted.

user1087973
  • 800
  • 3
  • 12
  • 29

1 Answers1

1

You can use the following solution:

SELECT m1.*, SUBSTRING_INDEX(GROUP_CONCAT(m2.name ORDER BY m2.age DESC), ',', 3) AS 'SOME_LIST'
FROM my_table m1 LEFT JOIN my_table m2 ON m1.name <> m2.name
GROUP BY m1.ID

demo: http://sqlfiddle.com/#!9/b56945/38/0

To get a comma-seperated list of the names you can use GROUP_CONCAT:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(name ORDER BY age DESC), ',', 3) AS 'SOME_LIST' 
FROM my_table 
WHERE name <> 'John';

With the SUBSTRING_INDEX you can limit the number of comma-seperated values. You can also ORDER BY the values directly on the GROUP_CONCAT.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87