'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;

mysqlsqldatabaseoracle


Solution 1:[1]

Try with this for MySQL:

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

Solution 2:[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:[3]

You can try this command:

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

Solution 4:[4]

Use:

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

Solution 5:[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:[6]

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 7:[7]

Kindly try the below one,

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

Solution 8:[8]

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 9:[9]

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

for MySQL.

Solution 10:[10]

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 11:[11]

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 12:[12]

In case of Oracle

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

Solution 13:[13]

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 14:[14]

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 15:[15]

For SQL Server try this:

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

Solution 16:[16]

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 17:[17]

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 18:[18]

This query also works for me

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

Solution 19:[19]

For MySQL: 

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

Solution 20:[20]

HackerRank SQL question.

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

Solution 21:[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:[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:[23]

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

Solution 24:[24]

It Works for mySql

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

Solution 25:[25]

For DB2:

select name from students where marks > 75 order by substr(name,length(name)-2,3),ID ASC;

Solution 26:[26]

Instead of SUBSTR() like other people here, I used the RIGHT() function and it worked for me using MySQL. Here's the complete SQL code:

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

Solution 27:[27]

IN ORACLE SQL, based on mysql answers seen here

SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY SUBSTR (NAME, LENGTH(NAME)-2, 3) ASC;

Solution 28:[28]

I made this test on RackerRank on 04-25-2022, with the code:

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

I get the following error:

ERROR 3065 (HY000) at line 4: Expression #2 of ORDER BY clause is not in SELECT list, references column 'students.ID' which is not in SELECT list; this is incompatible with DISTINCT

When I added the id on the select (select id, name...) I get a "wrong answer" as a result. The expected answer does not include the id field.

The only way I got it, was:

select main.name from
    (select distinct id, name from students where marks > 75 order by right(name,3), id asc) as main;

Solution 29:[29]

Try this:

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

Related Questions