the output from the MySQL and the Oracle SELECTS Hackerrank THE PADS-CodePudding

i try to solve the pads challenge in hackerrank : https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true

Mysql :

SELECT CONCAT(Name, '(', LEFT(Occupation,1), ')') FROM OCCUPATIONS 
ORDER BY Name asc;
SELECT CONCAT('There are a total of ', count(name), ' ', LOWER(occupation),'s.' )
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY count(name);

Compiler

You have passed the sample test cases.

Your Output (stdout)

Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
Eve(A)
Jane(S)
Jennifer(A)
Jenny(S)
Julia(D)
Ketty(A)
Kristeen(S)
Maria(P)
Meera(P)
Naomi(P)
Priya(D)
Priyanka(P)
Samantha(A)
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.

Oracle :

SELECT Name || '(' || substr(Occupation,1,1) || ')' FROM OCCUPATIONS 
ORDER BY Name asc;
SELECT 'There are a total of ' || count(name) || ' ' || LOWER(occupation) || 's.'
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY count(name);

Compiler Message

Wrong Answer

Your Output (stdout)

Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
Eve(A)
Jane(S)
Jennifer(A)
Jenny(S)
Julia(D)
Ketty(A)
Kristeen(S)
Maria(P)
Meera(P)
Naomi(P)
Priya(D)
Priyanka(P)
Samantha(A)
There are a total of 3 doctors.
There are a total of 4 singers.
There are a total of 4 actors.
There are a total of 7 professors.

When i use mysql work as expected output, but when i try Oracle they give me "wrong answer" but i see the output seem too similiar. can somebody expert in oracle explain the problem to me?

CodePudding user response：

The explanation section of the problem states:

The results of the second query are ascendingly ordered first by number of names corresponding to each profession (2 <= 2 <= 3 <= 3), and then alphabetically by profession (doctor <= singer, and actor <= professor).

In neither of the solutions do you guarantee to generate the results with the correct ordering.

I don't know why the MySQL solution is being marked as correct (given your edit, it appears that MySQL is randomly producing the input rows in the correct order even when you are not using the correct ORDER BY clause and the output happening to be correct is purely coincidence) but it is right that the Oracle solution is incorrect as the rows are not in the correct order.