I was practicing SQL problems at hackerrank.com when I came across the following question:

Query the Name of any student in STUDENTS who scored higher than 75. The Marks field in the table stores the marks for student. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

To solve this, I put together a query with the help of this post:

select Name 
where Marks > 75 
order by case when substring(Name from -1 for 3) then 1 else 0 end, ID asc;

Now I am not sure that it actually does what the question is asking since, I have no idea idea of using case in queries. Any help will be greatly appreciated.

  • 2
    You don't need a CASE expression. (And we frown on questions on homework.)
    – Rick James
    Aug 6 '19 at 23:55
  • @RickJames thanks for your comments. Not a homework question fyi; I was just practicing SQL ony own when I stumbled upon this. Aug 7 '19 at 5:36

Your query is wrong.

case when substring(Name from -1 for 3) 
     then 1 
     else 0 end

The expression substring(Name from -1 for 3) returns one last symbol from Name (if exists). Not 3 last as you want...

So the whole expression returns 0, when Name is NULL, empty string, or when the last symbol in Name is not a digit rather than 0. And 1 otherwise (when last symbol exists, and it is a digit from 1 to 9).

Correct query (looking on your query) is

FROM Students
WHERE Marks > 75 
         id ASC;
  • Marks is a field name with integer values that represents the student's marks; marks has to be greater than 75 for the rest of the query to yield the correct result in this case (all students with marks > 75, sorted in ascending order using last three digits of name; if sorting fails because of duplicates then sort using the ID in ascending order) Aug 7 '19 at 5:40
  • thanks for the solution. worked perfectly! Aug 7 '19 at 5:46
  • 1
    @AbrarHossain Remember that in my query the records where Name field length is less than 3 will be ordered like they have additional trailing spaces. I don't know does it is correct, if not you must add leading spaces implicitly (ORDER BY RIGHT(CONCAT(REPEAT(' ', 3),Name), 3)). And the records where Name is null will be first, if it is not correct than add previous ordering level by Name IS NULL which will move NULLs last.
    – Akina
    Aug 7 '19 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.