Selecting more than the LIMIT using DISTINCT?

Databases
1

Hi!

I have:


table mytable:
id, name, property
1, admin, admin area
1, admin, template area
1, admin, resource area
2, designer1, template area
2, designer1, resource area
3, publisher, null
4, designer2, template area
4, designer2, admin area

I know this is not relational but I used a table join. I didn’t show it here for simplicity.

I want to do something like this:


1, admin, admin area + template area + resource area
2, designer1, template area + resource area
3, publisher, null
4, designer2, templatea area + admin area
  1. How do I concatenate rows in SQL? (It seems difficult, for I have googled (It is ok, since I managed to do it in Java, inefficient, though))
  2. More importantly: How do I set the limit so that the rows I get back is distinct rows is more than the actual rows because of the duplicates? For example: if I limit to 2, I do not get only id = 1. I get id = 1 and 2.

I have tried:

SELECT * FROM mytable IN (SELECT * FROM (SELECT DISTINCT id FROM mytable LIMIT 0, 2) alias)

but it doesn’t work. Please help. Thanks.

2 

SELECT
      m.id
      ,m.name
      ,GROUP_CONCAT(m.property SEPARATOR ' + ') sections
  FROM
      mytable m
 GROUP
    BY
      m.id
LIMIT
      10

Do know that GROUP_CONCAT() has a limit. Depending on the amount of data being concatenated together it may be ~safer~ choice to handle this within the application language for that reason.

3

or, I seem to recall, you can increase the max_string_length so that the limit on cancat-returned data is increased…

bazz

4

Thanks oddz.

Thanks IBazz.

5

Hi!

Is it possible to sort the GROUP_CONCAT list into alphabetical order ascending?

Now I am getting:

admin + template area + resource area
admin + resource area + template area

for some records.

Thanks.

6

yes, it is possible to sort the concatenated strings

the syntax for doing so is clearly explained in the manual

:slight_smile: