How to rewrite a statement, that uses the MySQL Extensions to GROUP BY to a standard SQL statement?

Question

It ueses the MySQL Extensions to GROUP BY and cannot be executed, if the sql_mode is set to ONLY_FULL_GROUP_BY.

-- SET sql_mode = ""; -- reset sql_mode and execute statement
SELECT
    `courses`.`id` AS `id`,
    `courses`.`title` AS `title`,
    `courses`.`description` AS `description`,
    MATCH (coursedata.title) AGAINST ('Salsa') * 5 + MATCH (coursedata.description) AGAINST ('Salsa') * 2 AS `relevance`
FROM `courses`
INNER JOIN `coursedata` ON `courses`.`id` = `coursedata`.`id`
GROUP BY `courses`.`id`
HAVING `relevance` >= '3'

Is it possible to reach the same effect with standard SQL?

Why are you grouping by in the first place? You're not using agregation functions. your having clause should be a WHERE clause and the group by could be removed. — Sebas, Apr 15 '13 at 22:56
It's non-standard SQL so there is no standard for it ;) You can get close by adding everything to your `GROUP BY` or adding `MAX()` everywhere. — Wolph, Apr 15 '13 at 22:58
@Sebas You're right, here `GROUP BY` doesn't really make sense. But it's just the "core" a longer SQL statement, where I have to `GROUP BY` in irder for filter duplicates out. — automatix, Apr 15 '13 at 23:02
DISTINCT filters, group by groups, that's different! See my answer there if you wish to know more: http://stackoverflow.com/a/11416345/1291428 — Sebas, Apr 15 '13 at 23:04

automatix · Answer 1 · 2013-04-15T23:32:40.660

0

Seems to work...

SELECT * FROM (
    SELECT
        DISTINCT `courses`.`id` AS `id`,
        `courses`.`title` AS `title`,
        `courses`.`description` AS `description`,
        MATCH (coursedata.title) AGAINST ('Salsa') * 5 + MATCH (coursedata.description) AGAINST ('Salsa') * 2 AS `relevance`
    FROM `courses`
    INNER JOIN `coursedata` ON `courses`.`id` = `coursedata`.`id`
) AS subselect
WHERE `relevance` >= '3'

edited Apr 15 '13 at 23:32

answered Apr 15 '13 at 22:59

automatix

14,018
26
105
230

here you go! What about passing the where inside the subquery? It would be more logically correct I guess. – Sebas Apr 15 '13 at 23:00
I's not possible: `Error Code: 1054 Unknown column 'relevance' in 'where clause'` That was, why I was using `HAVING`. – automatix Apr 15 '13 at 23:08
I see. Using alias within where clause is forbidden probably. Would have to rewrite the whole match line then... – Sebas Apr 15 '13 at 23:10

How to rewrite a statement, that uses the MySQL Extensions to GROUP BY to a standard SQL statement?

1 Answers1