Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

1. ## group_concat order by

Code:
```a        b               c
en      animal           1
en      beast            null
sl      žival            1
sl      zverina          null
de      tier             null```
expected result:
"žival,animal,zverina,beast,tier"

1.That is, the pripority takes languages in this order: sl,en,de
2.and before that the priority takes c column, taking in acount previous rule.

Have something like this but of course it does not work according to task
Code:
`group_concat(distinct b order by (case a when 'sl' then 1 when 'en' then 2 when 'de' then 3 else 4 end)) as ord_by`

• You don't say whether or not c will have any values besides 1 and NULL.

So I have to assume that c might be NULL or might be any number, at all.

Code:
```GROUP_CONCAT(
DISTINCT b
ORDER BY
IF( c IS NULL, 1, 0 ) ASC,
IFNULL( c, 0 ) ASC,
IF ( 'sl', 1, IF( 'en', 2, IF( 'de', 3, 4 ) ) ) ASC
) AS whatever```
You may wonder about the `IFNULL( c, 0 )`: Won't that put a NULL value of c *after* a value of (say) -7? But no, we ensured that all the NULL values come *AFTER* all non-null values by the previous `IF ( c IS NULL, 1, 0 )`

NOTE: If you *ONLY* have values of NULL and 1 for [b]c[/c] then you can simplify that to:
Code:
```GROUP_CONCAT(
DISTINCT b
ORDER BY
IF( c IS NULL, 1, 0 ) ASC,
IF ( 'sl', 1, IF( 'en', 2, IF( 'de', 3, 4 ) ) ) ASC
) AS whatever```
And, yes, we could use CASE WHEN in place of the nested IF's.

• Nope.

expected result:
"žival,animal,zverina,beast,tier"
test1 table & sample data:

Code:
```DROP TABLE IF EXISTS `test1`;
CREATE TABLE IF NOT EXISTS `test1` (
`a` varchar(50) COLLATE utf8_slovenian_ci NOT NULL,
`b` varchar(50) COLLATE utf8_slovenian_ci DEFAULT NULL,
`c` varchar(50) COLLATE utf8_slovenian_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;

DELETE FROM `test1`;

INSERT INTO `test1` (`a`, `b`, `c`) VALUES
('sl', 'zverina', NULL),
('sl', 'žival', '1'),
('en', 'animal', '1'),
('en', 'beast', NULL),
('de', 'tiere', NULL);```
The ,c column 1 marked, should come first and those ordered by 'sl','en','de'
then all the rest ordered by 'sl','en','de'

To make it more clear. I'm making seo string, which length is limited (auto cut off by search engines), so I'm putting most used words ('in all languages') for same thing in front of SEO string.
Like animal is more comonly used comparing to beast.

• Oh, I'm an IDIOT!

Code:
```SELECT
GROUP_CONCAT(
DISTINCT b
ORDER BY
IF( c IS NULL, 1, 0 ) ASC,
IF ( a='sl', 1, IF( a='en', 2, IF( a='de', 3, 4 ) ) ) ASC
) AS whatever
FROM test1;

+----------------------------------+
| whatever                         |
+----------------------------------+
| zival,animal,zverina,beast,tiere |
+----------------------------------+```
I omitted the a= part of each IF test!!

If you prefer:
Code:
```mysql>  SELECT
->     GROUP_CONCAT(
->         DISTINCT b
->         ORDER BY
->              IF( c IS NULL, 1, 0 ) ASC,
->              CASE a WHEN 'sl' THEN  1 WHEN 'en' THEN 2 WHEN 'de' THEN 3 ELSE 4 END ASC
->         ) AS whatever
-> FROM test1;
+----------------------------------+
| whatever                         |
+----------------------------------+
| zival,animal,zverina,beast,tiere |
+----------------------------------+```

• You know, a MUCH better way of doing this in any case would be to use a lookup table.

Example:
Code:
```mysql> select * from languagePriority;
+------+----------+
| lang | priority |
+------+----------+
| sl   |        1 |
| en   |        2 |
| de   |        3 |
+------+----------+

mysql>  SELECT
->     GROUP_CONCAT(
->         DISTINCT b
->              ORDER BY IF( c IS NULL, 1, 0) ASC, IFNULL(p.priority,999999) ASC
->     ) AS whatever
-> FROM test1 LEFT JOIN languagePriority AS p ON p.lang = test1.a ;
+----------------------------------+
| whatever                         |
+----------------------------------+
| zival,animal,zverina,beast,tiere |
+----------------------------------+```

• ## Users who have thanked Old Pedant for this post:

BubikolRamios (08-20-2013)

• Encountered a problem in this part:

Code:
`GROUP_CONCAT( DISTINCT b ORDER BY IF ( c IS NULL ...`
suppose that I have data like this:

Code:
```b     c

foo  null
foo  1```
DISTINCT b

will eat out one of those records completely (before running order by), randomly I think,
and even that I have record with 1,it could/will not pop out in result.

Any thoughts ?

• You have conflicting data, so of course it's not going to work.

It's the same problem that all MySQL queries using GROUP BY have when you don't include *all* the non-aggregate columns in the GROUP BY. In any other DB, you would get an error. MySQL allows it but then picks a value at random from the choices that are not specified in the GROUP BY.

You need to disambiguate the data.

Something like this:
Code:
```SELECT
GROUP_CONCAT(
b
ORDER BY
c DESC,
IF ( a='sl', 1, IF( a='en', 2, IF( a='de', 3, 4 ) ) ) ASC
) AS whatever
FROM ( SELECT a, b, MAX(IFNULL(c,0)) AS c FROM test1 GROUP BY a, b ) AS X;```
Notice that you no longer need the DISTINCT, because the *CORRECT* GROUP BY has been used in the inner SELECT.

•

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•