[Solved] Query to order by the last three characters of a column

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.

STUDENTS table has following columns:

ID , NAME , MARKS

Sample input:

id         name     marks
1          ashley   81
2          samantha 75
3          julia    76
4          belvet   84

Sample output:

Ashley
Julia
Belvet

Explanation:

Only Ashley, Julia, and Belvet have marks > 75. If you look at the last three characters of each of their names, there are no duplicates and 'ley' < 'lia' < 'vet'.

This is correct output:

select name from students where marks>75

order by substr(name, -3, 3), id;

Solution #1:

Try with this for MySQL:

SELECT NAME FROM STUDENTS WHERE Marks > 75 ORDER BY RIGHT(NAME, 3), ID ASC;
Respondent: hizbul25

Solution #2:

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. That’s why ORDER BY ID is needed.

For MySQL:

SELECT Name FROM STUDENTS WHERE Marks>75 ORDER By SUBSTRING(Name,-3,LENGTH(Name)),ID

Reference: MySQL SUBSTRING() function

For Oracle:

SELECT Name FROM Students WHERE Marks > 75 ORDER BY substr(Name, -3), ID;
Respondent: Rohan Khude

Solution #3:

You can try this command:

SELECT * FROM students ORDER BY RIGHT(name, 3), ID
Respondent: TonyB

Solution #4:

DISTINCT should be removed. Otherwise multiple students with the same NAME would be ignored.

SELECT NAME
FROM STUDENTS
WHERE MARKS > 75
ORDER BY SUBSTRING(NAME, LEN(NAME)-2, 3), ID;
Respondent: Oleksandr Diachuk

Solution #5:

You can try below query:

SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY SUBSTRING(NAME,-3), ID;

NOTE: This one will work for MySQL

Respondent: Dharmil Thakkar

Solution #6:

Kindly try the below one,

SELECT Name FROM STUDENTS WHERE Marks >75 ORDER BY substr(name,-3,3),Id;
Respondent: Praveen Kumar C

Solution #7:

The best one is like this:

Query for output

select name from students where marks > 75 ORDER BY substr(name,-3) asc, id asc;
Respondent: Rishabh Shivhare

Solution #8:

Oracle 11g Setup

CREATE TABLE students ( id, name, marks ) AS
  SELECT 1, 'Alice',     76 FROM DUAL UNION ALL
  SELECT 2, 'Beatrice',  76 FROM DUAL UNION ALL
  SELECT 3, 'Carol',     78 FROM DUAL UNION ALL
  SELECT 4, 'Denis',     80 FROM DUAL UNION ALL
  SELECT 5, 'Edward',    43 FROM DUAL UNION ALL
  SELECT 6, 'Fiona',    100 FROM DUAL UNION ALL
  SELECT 7, 'Gareth',     75 FROM DUAL;

Query:

SELECT Name
FROM   students
WHERE  Marks > 75
ORDER BY SUBSTR( name, -3, 3 ), ID;

Results:

NAME   
--------
Alice    
Beatrice 
Denis    
Fiona    
Carol 
Respondent: MT0

Solution #9:

In case of Oracle

SELECT NAME
FROM STUDENT
WHERE MARKS>75
ORDER BY SUBSTR(NAME,-3),ID;
Respondent: Rahul Vedanta

Solution #10:

SQL Server:

SELECT Name from STUDENTS
WHERE MARKS > 75
ORDER BY RIGHT(NAME, 3), RIGHT(NAME, 2), RIGHT(NAME, 1), ID

And it worked fine with me!

Respondent: Ashwini M

Solution #11:

The query below works for SQL Server:

SELECT name FROM Students
WHERE marks > 75
ORDER BY SUBSTRING(name, len(name)-2, LEN(name)), id;

Active reading [https://en.wikipedia.org/wiki/Microsoft_SQL_Server].

Respondent: Divya Gandivalasa

Solution #12:

Please try the below query. It works fine in SQL Server.

SELECT
     name
FROM
     students
WHERE
     marks > 75
ORDER BY
     RIGHT(name, 3) ASC
   , id ASC

Solution #13:

SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME, 3), ID ASC;

for MySQL.

Respondent: Nishit

Solution #14:

Use:

select name from students where marks > 75 order by right(name, 3), ID;
Respondent: Sachin

Solution #15:

It could be this using the SUBSTR function in MySQL:

SELECT `name`
FROM `students`
WHERE `marks` > 75
ORDER BY SUBSTR(`name`, -3), ID ASC;

SUBSTR(name, -3) will select the last three characters in the name column of the student table.

Respondent: Aashish Chaubey

Solution #16:

I submitted both of these solutions on HackerRank and they got accepted:

MySQL

SELECT name FROM students WHERE marks > 75 ORDER BY SUBSTRING(name, -3, 3), id

Oracle

SELECT name FROM students WHERE marks > 75 ORDER BY SUBSTR(name, -3, 3), ID;

Explanation:
The function SUBSTRING() or SUBSTR() has the following syntax:

SUBSTRING( string, start_position, length ) where length is the length of substring to be extracted from the string.

So, the above query lists the names of students who scored more than 75 marks, lexicographically based on the last 3 characters of their names. If two or more students have the same last 3 characters in their names (eg. Bobby, Robby), then their names are printed based on their ID in ascending order.

Respondent: SHIVANGI BALODIA

Solution #17:

This query also works for me

select Name from STUDENTS where Marks > 75 ORDER BY SUBSTRING(NAME, LENGTH(NAME)-2, 3), ID;
Respondent: Ahmed Al Bermawy

Solution #18:

For MySQL:

SELECT 
  name 
FROM 
  STUDENTS 
WHERE 
  marks > 75 
ORDER BY 
  RIGHT(name, 3), 
  RIGHT(name, 2), 
  id;
Respondent: Navya Gunasekaran

Solution #19:

HackerRank SQL question.

select distinct Name from STUDENTS where Marks > 75
order by substr(Name, -3, length(Name)), ID

Solution #20:

For SQL Server try this:

SELECT NAME FROM STUDENTS WHERE Marks > 75 ORDER BY SUBSTRING(NAME, LEN(NAME)-2, 3), ID ASC;
Respondent: dragonal

Solution #21:

Use:

SELECT NAME FROM STUDENTS WHERE marks > 75 ORDER BY SUBSTRING(name, -3, 3), id;

SUBSTRING(name, -3, 3) will take the last three characters from column(name) and with the help of ORDER BY column will be sorted in lexicographical order based on the last three characters. If the last three characters are same then it will ordered according to ID.

Respondent: Abhijeet Adhikary

Solution #22:

For MySQL you can try:

select Name from STUDENTS where Marks > 75 order by RIGHT(name, 3), ID;

For Oracle try the following:

select Name from STUDENTS where Marks > 75 order by substr(name, -3), ID;

Respondent: Vikram Ojha

Solution #23:

SELECT Name FROM STUDENTS 
WHERE
MARKS > 75 
ORDER BY LOWER(SUBSTR(NAME, - 3, 3)) ASC , ID ASC;
Respondent: sudipt dabral

Solution #24:

Try this:

SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY SUBSTRING (NAME, LEN(NAME)-2 ,LEN(NAME)), ID
Respondent: Akhila Veldandi

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.