mysqlgroup-concat

Select first and last row from group_concat when grouping sortable days

Referring to my previous questions about the group concat Mysql again, group by and display rest of rows

i need to get first and last day from that query

for example

row 3 from 8,9,10 to first collumn 8, last collumn 10
row 5 from 21,22,23,24,28,29,30 to first collumn 21, last collumn 30
row 6 from 17,21,22,23,24,25 to first collumn 17 last collumn 25

SUBSTR(GROUP_CONCAT(DAY),-1) as fl

BUT it gives me last char, and there are few rows with 1 or 2 chars for example

1,2,3,22
1,3,6,3

In first example it gaves me 2, not 22 :/

Solution

  • Don't waste your time trying to parse the first and last off of GROUP_CONCAT(). Instead, just stick the MIN() and MAX() together with CONCAT().

    SELECT 
  user,
  CONCAT(MIN(DAY)), ',', MAX(DAY)) AS f1
FROM yourtable
GROUP BY user