MySQL error 'with base as' subqueries and GROUP BY

All we need is an easy explanation of the problem, so here it is.

I don’t know why the company it is using such an old version of MySQL, but it is: 5.6.

We have some duplicate subdomains. For instance, given a subdomain of fastelvis we get 6 results, but there should only be one, the one that has lowest ID of the 6. This query works correctly and shows me the duplicate subdomains:

select subdomain, ID 
from clients where subdomain in 
    (select subdomain 
    from clients 
    group by subdomain 
    having count(*) > 1 ) 
order by subdomain, FSID ;

That works great.

The ID is an integer field, auto-incrementing.

But I need to find the lowest ID of any set of duplicates. That is, if fastelvis has IDs of 11, 27, 94, 149, 2398, and 9322, then I need to find just 11.

I thought this would get me what I want:

With base as (
select subdomain,
       ID
from clients
where subdomain in 
    (select subdomain 
    from clients 
    group by subdomain 
    having count(*) > 1 )
order by subdomain, ID)
SELECT subdomain,
       MIN(ID) first_subdomain_ID
FROM base
GROUP BY subdomain

I’ve been looking at this for 20 minutes and I don’t see any syntax errors, but I get:

You have an error in your SQL syntax; 
check the manual that corresponds to your
MySQL server version for the right syntax 
to use near 'base as ( select subdomain,

I’m left wondering, maybe this syntax doesn’t work in this version of MySQL?

Does anyone see a syntax error?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

select subdomain,
       MIN(id) AS min_id
    from clients 
    group by subdomain 
    having count(*) > 1

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply