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

Solution #1:

Try with this for MySQL:

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

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;

Solution #3:

You can try this command:

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

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;

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

Solution #6:

Kindly try the below one,

SELECT Name FROM STUDENTS WHERE Marks >75 ORDER BY substr(name,-3,3),Id;

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;

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 

Solution #9:

In case of Oracle

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

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!

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].

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.

Solution #14:

Use:

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

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.

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.

Solution #17:

This query also works for me

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

Solution #18:

For MySQL:

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

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;

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.

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;

Solution #23:

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

Solution #24:

Try this:

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