I am new to Hackerank, so I am not sure if this is something I missed or system-wide.
Use this as an example - https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true
select * from wands is fine
But...
with cte_t as
(select * from wands)
select * from cte_t
gives me this error
ERROR 1064 (42000) at line 28: 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 'cte_t as(select * from wands)select * from cte_t' at line 1
I have been trying to solve SQL without CTE, but I am at the point this is not possible without it.
//////////////////////////////
Edits:
Some comments led me to try other compiler, so by using "MS SQL Server" instead solves the problem that "MySQL" has as above.
//////////////////////////////
Another edits:
Still having issues with CTE
The following is fine:
select
sub.id, sub.age, min(sub.coins_needed) as min_coins_needed, sub.power
from (
select
w.id, wp.age, w.coins_needed, w.power
from wands as w
left join wands_property as wp
on w.code = wp.code
where wp.is_evil = 0
) as sub
group by sub.id, sub.age, sub.power
order by sub.power desc, sub.age desc
But this is not:
with fullt as (
select
sub.id, sub.age, min(sub.coins_needed) as min_coins_needed, sub.power
from (
select
w.id, wp.age, w.coins_needed, w.power
from wands as w
left join wands_property as wp
on w.code = wp.code
where wp.is_evil = 0
) as sub
group by sub.id, sub.age, sub.power
order by sub.power desc, sub.age desc
)
select * from fullt;
and giving this (uninformative) error message:
Msg 156, Level 15, State 1, Server dbrank-tsql, Line 16Incorrect syntax near the keyword 'select'.Msg 102, Level 15, State 1, Server dbrank-tsql, Line 28Incorrect syntax near ')'.
Videos
I encountered a similar situation where my CTE didn't work in HackerRank's MySQL environment. However, I have an interesting observation which is that if you switch from MySQL to MySQL server, then the CTE works, as indicated by the screenshot.
Note that the query provided by the op is not the final answer to the challenge. Rather it was used by the op to demonstrate the issue. So while the answer to the challenge is not correct, the query nevertheless runs in the MySQL Server environment.
This might not address the issue in the MySQL setting, but at least it allowed me to move forward with the code challenge.
Put the CTE into a subquery:
SELECT TOTAL_SUBMISSIONS.*
FROM (SELECT * FROM View_Stats) AS TOTAL_SUBMISSIONS
I have used a nested case statement :
1st case statement checks for if the number is a root or not
2nd case statement check whether n is in p to check it is a leaf or inner node
The following is the error message. First link is my code, second link is the hackerrank question. Thank you in advance.
Error Message :
ERROR 1064 (42000) at line 1: 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 'case
when n is not in (select p from bst)
then 'Leaf'
' at line 4
https://paste.ofcode.org/m2D6x27U8wiZUcVYXTnKKk
https://www.hackerrank.com/challenges/binary-search-tree-1/problem?isFullScreen=true
At first glance, WITH clause in your code looks correct. However, I did not check it in detail, so you shouldn't rely on my answer in this sense.
To answer the second part of your question, i.e. why you are getting that error from HackerRank, my understanding is: HackerRank's version of MySQL doesn't support CTEs (i.e. WITH clause). If you run
SELECT Version();
it prints out 5.7.27 . And according to this post, MySQL 5.7 doesn't support CTEs
If you're creating a CTE, you're creating a psuedo-table with column names.
In your first CTE, you haven't given MAX(SCORE) a column name (or alias). Second CTE is fine.
Change :-
WITH
MAX_POINT
AS
(
SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE)
FROM SUBMISSIONS
GROUP BY HACKER_ID, CHALLENGE_ID
)
To
WITH
MAX_POINT
AS
(
SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS HIGHSCORE
FROM SUBMISSIONS
GROUP BY HACKER_ID, CHALLENGE_ID
)
Hi friends
I was solving this hacker rank sql problem " Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates."
and my solution was
"
Select Distinct CITY From STATION
WHERE City LIKE '[!aeiou]%' ;
"
But for some reason it is not working IS there any mistake that I'm doing , Please let me know !!!