Syntax differences between Mysql and SQL Server

Question

Description:
Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Here is my SQL code:

SELECT 
    H.HACKER_ID AS 'HACKER_ID', NAME, 
    COUNT(NAME) AS 'COUNT1' 
FROM 
    HACKERS H
JOIN 
    CHALLENGES C ON H.HACKER_ID = C.HACKER_ID
GROUP BY 
    H.HACKER_ID, NAME
HAVING 
    COUNT1 = (SELECT MAX(S1.COUNT3) 
              FROM
                  (SELECT COUNT(HACKER_ID) AS 'COUNT3' 
                   FROM CHALLENGES
                   GROUP BY HACKER_ID) AS S1)
    OR COUNT1 IN (SELECT S2.COUNT2 
                  FROM 
                      (SELECT HACKER_ID, COUNT(HACKER_ID) AS 'COUNT2' 
                       FROM CHALLENGES
                       GROUP BY HACKER_ID) AS S2
GROUP BY 
    S2.COUNT2
HAVING 
    COUNT(S2.COUNT2) = 1)
ORDER BY 
    COUNT1 DESC, H.HACKER_ID;

Question:

Actually this code can run in Mysql environment and got the correct result but can't run in SQL Server environment and I get an error

Invalid column name 'COUNT1'

I would like to ask what's the exact error here in SQL Server and what's the difference between the Mysql and SQL Server because this error happens only in SQL Server but not in Mysql

score 2 · Accepted Answer · answered Jul 29 '20 at 23:09

As far as your query is concerned: SQL Server does not support reusing aliases defined in the FROM clause in the HAVING clause. In that regard, SQL Server follows the SQL standard, which MySQL does extend. You need to repeat the expression, which means: replace COUNT1 in the HAVING clause with COUNT(NAME).

I would also strongly suggest not surrounding the identifiers (here, the aliases in the SELECT clause) with single quotes. This is not standard SQL, and not supported by all databases. As long as your identifiers do not contain special characters, there is just no need to quote them.

Finally: your query looks more complicated that it needs to be. If you were to ask another question, with a minimal reproducible example (sample data from the related tables and expected results), then one might be able to suggest further optimizations. But this is not the question you asked here.

Syntax differences between Mysql and SQL Server

1 Answers1