0

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?

2

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.