Query the name of any student in students who scored higher than 75 marks. 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.
Input format
The STUDENTS table is described as follows:
Column | Type |
---|---|
ID | Integer |
Name | String |
Marks | Integer |
The Name column only contains uppercase(A-Z) and lowercase(a-z) letters.
Sample Input:
ID | Name | Marks |
---|---|---|
1 | Ashley | 81 |
2 | Samantha | 75 |
4 | Julia | 76 |
3 | Belvet | 84 |
Sample Output:
Ashley
Julia
Belvet
Explanation:
Only Ashley, Julia, and Belvet have Marks > . If you look at the last three characters of each of their names, there are no duplicates and ‘ley’ < ‘lia’ < ‘vet’.
To solve this query I am using ORDER BY RIGHT() function to order by last 3 chars in MySQL.
Syntax:
select * from table_name
where condition order by right (column_name, 3) sorting_order;
Solution:
select Name from STUDENTS
where Marks > 75
Order by RIGHT (Name, 3), ID asc;
Explanation:
select Name from STUDENTS where Marks > 75
the above query is part of the main query which will give us all the students who got 75 marks.
Order by RIGHT (Name, 3),
this part sorts the student’s name order by the last 3 letters.
ID, asc
this part is used when the last 3 letters are similar in 2 words then the name will sort by ID.
I hope you understand this query explanation.