How to insert max 3 results in different columns?

Question

I would like to insert max 3 results of a column into 3 different columns.

SELECT
    t.name,
    m.top_marks 
FROM
    table_name t,
    (SELECT
         marks
     FROM table_name
     WHERE rownum <=3
     ORDER BY marks DESC) m
GROUP BY column_name DESC;

This can help me to get top 3 marks right? but what if i want to store top 3 results in 3 new columns? Marks1, marks2, marks3?

[Pivot](http://stackoverflow.com/questions/7674786/mysql-pivot-table)? I imagine you may need to add something like a row-number field for this to work. — Bernhard Barker, Aug 21 '13 at 15:26

score 0 · Answer 1 · edited May 23 '17 at 12:11

0

I can't say I really understand what you are going for... But it sounds like you might want the LIMIT operator. See this:

SQL - Select first 10 rows only?

So maybe something like this?

SELECT
    t.name,
    m.top_marks 
FROM
    table_name t,
    (SELECT
         marks
     FROM table_name
     ORDER BY marks DESC
     LIMIT 3) m
GROUP BY column_name DESC;

edited May 23 '17 at 12:11

Community

1
1

answered Aug 21 '13 at 15:24

eatonphil

13,115
27
76
133

score 0 · Answer 2 · answered Aug 21 '13 at 15:38

You can do this with the group_concat()/substring_index() method:

SELECT t.name,
       substring_index(m.marks, ',', 1) as Mark1,
       substring_index(substring_index(m.marks, ',', 2), ',', -1) as Mark2,
       substring_index(substring_index(m.marks, ',', 3), ',', -1) as Mark3
    m.top_marks 
FROM table_name t cross join
     (SELECT group_concat(marks order by marks desc) as marks
      FROM table_name
     ) m
GROUP BY column_name DESC;

If it is sufficient to have them in one column with commas separating the values:

SELECT t.name, substring_index(m.marks, ',', 3) as Marks3
FROM table_name t cross join
     (SELECT group_concat(marks order by marks desc) as marks
      FROM table_name
     ) m
GROUP BY column_name DESC;

How to insert max 3 results in different columns?

2 Answers2