Question 20288 in Mysql Question posted in
The official MySQL documentation can be found here.

Order by query doesn't affect the result on THE PADS Hackerrank challenge – Mysql

right now im still practice sql. and try to solve challenge on hackkerank but i got some question and problem

Here challenge that i take:

"THE PADS"

link : https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true

This challenge contains counting the number of people based on their job title and the goal is sort the number from smallest to greatest and after that, sort the occupation alfabeticaly.

Problem :

I tried to solve this by using ORDER BY but it doesn’t seem to affect sorting number on the ouput

QUERY:

(Select concat(name,'(',LEFT(Occupation,1),')') as name from occupations) union 
(select concat('There are a total of ', count(*),' ', lower(occupation), 's.') 
from occupations group by occupation order by count(*) asc, occupation asc)

OUTPUT:

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

Expected OUTPUT:

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.

Question:

  1. why my query doesn’t work on hackkerank? i tried similiar method on w3school playground seem work normaly.

Link playground w3school : https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_concat

the query that i try on w3school:

SELECT concat(count(*), ' ',country)
from customers
group by country
ORDER BY count(*) asc
  1. i try using someone solution, and its work but i dont understand why
(Select concat(name,'(',LEFT(Occupation,1),')') as name from occupations ) union 
( select concat("There are a total of ", count(Occupation)," ",lower(Occupation),"s.") 
from Occupations group by Occupation order by count(Occupation) ) ORDER BY name
  • when i delete order by count(Occupation) the query still working, why?
  • why using ORDER BY name instead of using `order by count(*)?

i will apreciate to any explanation.

Tags:

2

Answers


  1. 0

    Union of 2 sets ordered as required

    select case rowtype 
   when 1 then concat(name,'(',LEFT(Occupation,1),')' )
   else concat ('There are a total of ',cnt,' ', lower(occupation), 's.')  end name
from  (
     select 1 rowtype, name, 0 cnt, Occupation 
     from occupations

     union 
     (
     select 2, null, count(*), Occupation           
     from occupations 
     group by occupation
     ) 
) t
order by rowtype, name, cnt, occupation
    Login or Signup to reply.
  2. 0
    1 Samantha Doctor
    2 Julia Actor
    3 Maria Actor
    4 Meera Singer
    5 Ashely Professor
    6 Keety Professor
    7 Christeen Professor
    8 Jane Actor
    9 Jenny Doctor
    10 Priya Singer
    11 Liam Sportman

    Let’s split the queries:

    SELECT Name || "(" || SUBSTRING(Occupation, 1, 1) || ")" AS Jobs
FROM occupations_table
ORDER BY Name
    Jobs
    Ashely(P)
    Christeen(P)
    Jane(A)
    Jenny(D)
    Julia(A)
    Keety(P)
    Liam(S)
    Maria(A)
    Meera(S)
    Priya(S)
    Samantha(D) 
    SELECT "There are a total of " || COUNT(*) || " " || Occupation AS Report
FROM occupations_table
GROUP BY Occupation
ORDER BY COUNT(*), Occupation
    Report
    There are a total of 1 Sportman
    There are a total of 2 Doctor
    There are a total of 2 Singer
    There are a total of 3 Actor
    There are a total of 3 Professor

    You can’t UNION them with the group by or the whole query will fail, but you can ORDER BY after UNION and you should get the expected result, or at least i’m getting it by running the following code on a sqlite build

    SELECT Name || "(" || SUBSTRING(Occupation, 1, 1) || ")" AS Jobs
FROM occupations
UNION
SELECT "There are a total of " || COUNT(*) || " " || Occupation AS Quantity
FROM occupations
GROUP BY Occupation
ORDER BY Jobs, Quantity
    Jobs
    Ashely(P)
    Christeen(P)
    Jane(A)
    Jenny(D)
    Julia(A)
    Keety(P)
    Liam(S)
    Maria(A)
    Meera(S)
    Priya(S)
    Samantha(D)
    There are a total of 1 Sportman
    There are a total of 2 Doctor
    There are a total of 2 Singer
    There are a total of 3 Actor
    There are a total of 3 Professor
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search