Here is the query:
/*1st SELECTING THE VALUES requested to be printed*/
SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS challenge_counter
FROM hackers h
JOIN challenges c
ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
/*2nd applying the values found before*/
HAVING challenge_counter IN (
SELECT aux_table.counter
FROM(
SELECT hacker_id, COUNT(challenge_id) AS counter
FROM challenges
GROUP BY hacker_id
) AS aux_table
GROUP BY aux_table.counter
HAVING COUNT(aux_table.counter) = 1
)
OR
challenge_counter =(
SELECT MAX(aux_table.counter)
FROM(
SELECT hacker_id, COUNT(challenge_id) AS counter
FROM challenges
GROUP BY hacker_id
) AS aux_table)
/* Finally we order as requested (by counter and hacker_id)*/
ORDER BY challenge_counter DESC, h.hacker_id ASC;
Getting error like:
Msg 207, Level 16, State 1, Server dbrank-tsql, Line 16
Invalid column name 'challenge_counter'.
Msg 207, Level 16, State 1, Server dbrank-tsql, Line 27
Invalid column name 'challenge_counter'.
I did not figure out how would the column 'challenge_counter' be invalid? Please point it out.
