Order By First Condtition and If multiple matches, order by second condition

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
from STUDENTS
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.

• You don't need a `CASE` expression. (And we frown on questions on homework.) 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

``````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

``````SELECT Name
FROM Students
WHERE Marks > 75
ORDER BY RIGHT(Name, 3) ASC,
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
• @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. Aug 7 '19 at 6:05