Getting Last Value in Group Concat

Question

I have a table

course     chapter          lessons

  2        Chapter1          3,4
  2        Chapter2          5,10,9,6,8
  2        Chapter3          11,15,16,18

I need the last value in lessons column like In the 1st row of lessons i need the last digit 4
In the 2nd row of lessons i need the last digit 8
In the 3rd row of lessons i need the last digit 18

[Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad) Short answer: **Yes, it is**. — ypercubeᵀᴹ, Dec 28 '11 at 17:44
Until you normalize, use `SELECT SUBSTRING_INDEX(lessons, ',', -1) FROM table`. Check [String functions in MySQL](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index) — ypercubeᵀᴹ, Dec 28 '11 at 17:47
@ypercube: Why are the above 2 comments and not answers? You have it... `Select course, chapter, lessons, substring_Index(Lessons,',',-1) as LastLesson from table` — xQbert, Dec 28 '11 at 18:02
Hello ypercube, what;s there to be bad in showing group_concat? — ronquiq, Dec 28 '11 at 18:05
@ronquiq: Either it's a base table (and it's bad, really bad design) or the output of a query that uses `GROUP_CONCAT()`. Which one is it? — ypercubeᵀᴹ, Dec 28 '11 at 18:08

score 6 · Accepted Answer · edited May 23 '17 at 12:20

6

Is storing a comma separated list in a database column really that bad? Short answer: Yes, it is.

But until you normalize the table, you could use this:

SELECT course 
     , chapter
     , SUBSTRING_INDEX(lessons, ',', -1) AS last_lesson
FROM tableX

edited May 23 '17 at 12:20

Community

1
1

answered Dec 28 '11 at 18:07

ypercubeᵀᴹ

113,259
19
174
235

Getting Last Value in Group Concat

1 Answers1