Select first and last row from group_concat when grouping sortable days

Question

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 :/

score 13 · Answer 1 · answered May 25 '12 at 13:05

13

Another option (besides Michael's solution) is to use the SUBSTRING_INDEX:

SUBSTRING_INDEX(str,delim,count)

with

count = 1

you get the first day,

with

count=-1

you get the last one

answered May 25 '12 at 13:05

Th0rndike

3,406
3
22
42

This works and I thank you for it. However, I can't believe there is no way to do that without concatenating and string to then split it. That is a lot of string manipulation for something that was already at some point an array. – Adeynack Apr 27 '20 at 17:33

score 3 · Accepted Answer · answered May 25 '12 at 12:58

3

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

answered May 25 '12 at 12:58

Michael Berkowski

267,341
46
444
390

do the min and max behave like the "first" and "last" or do they behave like their name sounds like? – Jus12 Feb 09 '15 at 10:05
@Jus12 They are [the aggregate `MIN()/MAX()`](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html) so they are as they sound, the least and greatest values per `user` group. – Michael Berkowski Feb 09 '15 at 11:41
I like this, but the solution is very specific if the data is arranged by date and the question seems more generic "select first and last row from group concat". Ideally there should be a way to select the first and last elements without sorting. Is there anything like that? – Jus12 Feb 09 '15 at 13:37
No, except for performing string operations on the concatenation as in the other answer. Because aggregate functions are applied only once per group per `SELECT` level, to get the first or last you would need some additional aggregation, wrapping it in a subquery to retrieve the min/max, or subqueries to `ORDER BY/LIMIT` and extract only one row. All these solutions are ugly. I'll edit the question title to make it more specific to the use case. – Michael Berkowski Feb 09 '15 at 13:41
@Jus12 You should ask a new question if you are looking for a more generic use case – Michael Berkowski Feb 09 '15 at 13:43

Select first and last row from group_concat when grouping sortable days

2 Answers2