0

I'm doing an assignment which I tried to complete in SQL Server, as that's the language I will be using at my new job. I completed my query like this:

SELECT 
    h.hacker_id, h.name, sum(scr) AS tot
FROM
    hackers AS h 
JOIN 
    (SELECT 
         s2.hacker_id, s2.challenge_id, max(s2.score) AS scr
     FROM 
         submissions s2
     GROUP BY 
         s2.hacker_id, s2.challenge_id
     HAVING 
         scr > 0) AS CET ON h.hacker_id = CET.hacker_id
GROUP BY 
    h.hacker_id
ORDER BY 
    tot DESC, h.hacker_id;

This query works (as in, it's a correct solution) in MySQL, but not in SQL Server. I figured I'd use a CTE instead of a SELECT query in the JOIN statement, so I adjusted this to use CTE instead like this:

WITH CTE AS
(
    SELECT 
        s2.hacker_id, s2.challenge_id, max(s2.score) AS scr
    FROM 
        submissions s2
    GROUP BY 
        s2.hacker_id, s2.challenge_id
    HAVING 
        scr > 0
)
SELECT 
    h.hacker_id, h.name, sum(scr) as tot
FROM 
    hackers AS h 
JOIN 
    CTE ON h.hacker_id = CTE.hacker_id
GROUP BY 
    h.hacker_id
ORDER BY 
    tot DESC, h.hacker_id;

but this throws an error:

Invalid column name 'scr'.

(it says on Line 6 but from experience the website I use to complete these assignments can be a bit iffy with line indicators of errors).

Could someone please shed some light into what I have missed, and a working example for SQL Server?

(And of course, general criticism of the code itself is very much welcome, I want to become good at this).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cenderze
  • 1,202
  • 5
  • 33
  • 56

1 Answers1

4

Change to HAVING max(s2.score)>0

Because select get executed AFTER havingclause, which means src does not exist when having get exectued.

Updated answer:

SELECT h.hacker_id, h.name, sum(scr) AS tot
FROM hackers AS h JOIN (
     SELECT s2.hacker_id, s2.challenge_id, max(s2.score) AS scr
     FROM submissions s2
     GROUP BY s2.hacker_id, s2.challenge_id
     HAVING max(s2.score) > 0) AS CET
ON h.hacker_id = CET.hacker_id
GROUP BY h.hacker_id,h.name
ORDER BY tot DESC, h.hacker_id;

You got that error is becuase h.name is not in the group by clause, all columns need to be took care of by grouping methods

LONG
  • 4,490
  • 2
  • 17
  • 35
  • Because `select` get executed AFTER `having` which means `src` does not exist when `having` get exectued – LONG Jan 03 '17 at 20:56
  • 1
    If you would like to amend or extend your answer, then you should edit it, not comment on it. – John Bollinger Jan 03 '17 at 20:58
  • Using HAVING s2.score > 0 caused the error Column 'submissions.score' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Trying to remove "as scr" from the SELECT (both in the main SELECT statement and in the CET) clause didn't work either, with the same error. – Cenderze Jan 03 '17 at 21:03
  • @GustavDanell: I updated the answer, please try the updated one. – LONG Jan 03 '17 at 21:08
  • @LONG Thanks! Now it works! Strange that my solution passed in MySQL though. In the MySQL code I used scr in the HAVING clause. Do they differ in how they execute in some way? – Cenderze Jan 03 '17 at 21:24
  • 1
    Yes. MySQL extends the having http://dba.stackexchange.com/questions/50391/why-does-mysql-allow-having-to-use-select-aliases MySQL also extends the group by so you don't always have to use all columns in the group by; but that's not standard either (until recent patch/release which disables this feature) [mySQL Docs](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) so yes it behaves differently. – xQbert Jan 03 '17 at 21:25
  • @GustavDanell: check @xQbert's link, that explains perfectly why your previous code works in Mysql not sqlserver, pay attention to the execution order especially `having`, (before `select` in mysql; after `select` in sql server) – LONG Jan 03 '17 at 21:28