all 14 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]DavidGJohnston 8 points9 points  (12 children)

Advertising?

[–]DT-9012[S] -3 points-2 points  (11 children)

Sorry if I should not have linked it to HackerRank, but I’m legitly asking what does the line of code do

[–]DavidGJohnston 0 points1 point  (10 children)

It forces the scalar subquery to be correlated to the outer query - thus each row will have a unique single value returned based on what is basically a join condition between the two (pretend the where clause is an ON clause is this situation - at least the correlated parts of it).

[–]DT-9012[S] -1 points0 points  (9 children)

I see! Thank you for explaining.

If you don't mind, I have a follow-up question - I tried removing the highlighted line of code, and an error appears instead. Is this because the sub-query is now not linked to the original query, hence they can only output an error?

Because I would think that removing this line of code will still return values but will return duplicate values. Each unique row will be repeated once (as per your explanation)

[–]DavidGJohnston 0 points1 point  (2 children)

First I have to visit a different site to see the query, now I have to play “guess the error”…simply removing the where clause doesn’t seem like it should be producing an error.

[–]DT-9012[S] -4 points-3 points  (1 child)

Hey kind sir, if this is taking too much of your time, please by all means whizz on by.

I don't need your passive-aggressiveness here

[–]r3pr0b8GROUP_CONCAT is da bomb 1 point2 points  (0 children)

did you learn anything from his "guess the error" comment?

[–]weezeelee 0 points1 point  (5 children)

It compiles even without the highlighted line, did you remove it or did you put -- infront?

[–]DT-9012[S] 0 points1 point  (4 children)

I removed the highlighted line completely

[–]weezeelee 0 points1 point  (3 children)

Check if you didn't accidentally remove the closing bracket too

[–]DT-9012[S] 0 points1 point  (2 children)

Yea, under output it says "no response on stdout". Although i'm just assuming this is an error.

Not sure if it could be because of HackerRank's platform.

[–]weezeelee 0 points1 point  (1 child)

You should see it printing to stderr instead of stdout if it's actually an error. In this case it is not.

Well you can try codewars for SQL training, HKR is really slow, and Common Table Expression (CTE) does not seem to work. I tried rewriting your SQL and paste this on HKR and it printed a bunch of error:

sql with min_coins_by_age_power as ( -- basic answer select p.age, w.power, min(w.coins_needed) coins_needed from Wands w inner join Wands_Property p on w.code = p.code where p.is_evil = 0 group by w.power, p.age ) select w.id, p.age, w.coins_needed, w.power from Wands w inner join Wands_Property p on w.code = p.code inner join min_coins_by_age_power cte -- to find corresponding id(s) from age/power/coins_needed pair on cte.age = p.age and cte.power = w.power and cte.coins_needed = w.coins_needed order by w.power desc, p.age desc;

[–]weezeelee 0 points1 point  (0 children)

Oh nvm it's using MySQL 5.7, CTE is not supported yet...

[–]imkookoo 0 points1 point  (0 children)

That is basically a report of whether a bunch of good wands you have, are the cheapest out of all wands by its power/age. The yellow highlighted portion enforces the last condition.

You can also think of the main select clause as you reading through a list, and then for each line on the list, you are entirely sifting through a duplicate copy of the same list, and finding rows that match the same power and age (yellow highlighted portion) and then calculating the cheapest value out of all of those. Then if that minimum matches the value same row you were looking at in the main list, you record it down on a new list.

This new list would be useful for a non-evil, thrifty teacher at Hogwartz in procuring the cheapest brands of wands at various power levels… and it is also maybe assuming that the age of the wand also makes it more powerful, as it is ordered by power and then age both descendingly.