HackerRank 'Higher than 75 marks' MySQL and PostgreSQL

I'm trying to create the habit of revisiting exercises I've previously solved after some time, to check whether I'm truly retaining the concepts and topics I’ve studied.

When redoing a HackerRank exercise, I decided to solve it using a different approach — this time with ORDER BY RIGHT, which is valid in MySQL and useful when we want to sort a column by the end of the string values. I wasn’t aware of this usage before. So, the first time I solved the challenge, I used the SUBSTRING function instead.

This was the exercise:

It’s a simple task. It asks us to select the names of students who scored more than 75, and order the results by the last three characters of their names in ascending order. If two or more names end with the same three letters, then sort them by their ID in ascending order.
The difference lies in how the SUBSTRING function works in PostgreSQL compared to MySQL.

Here are some possible solutions:

Compatible with both MySQL and PostgreSQL:

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

Using RIGHT(column, n) returns the last n characters of a string. So, this query sorts by the last three letters of the name. Simple and straightforward.

SUBSTRING() in MySQL:

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

Testing in PostgreSQL via WSL
HackerRank doesn’t offer PostgreSQL as an option, but I was advised to test queries in the actual database environment. It takes more effort, but I find that it really helps me get used to the tool. Plus, new questions always come up — whether it's due to an error or a difference in PostgreSQL's syntax — and I end up learning beyond the scope of the original problem. And also, cause I believe this has helped me to absorb the concepts in a smooth way.

When I tried to use SUBSTRING the same way I did in the MySQL version, it didn’t work as expected in PostgreSQL.
It turns out that PostgreSQL doesn’t support negative indices in SUBSTRING. While it allows syntax like SUBSTRING(column, -X, Y), the behavior may not be what you'd expect.

In my local database, I created a station table with a city column and added some city names. To better visualize what was happening, I ran this query:

SELECT 
  city, 
  SUBSTRING(city, -3, 3) AS error,
  RIGHT(city, 3) AS certo
FROM station;

And this was the result:

So, PostgreSQL accepts the syntax, but returns an empty string because the negative index is ignored in SUBSTRING.

A simple way to view:

PostgreSQL does not interpret -3 as “from the end.” It didn’t throw an error — just returned an empty string.

Using SUBSTRING correctly:

This returns the last 3 characters as expected.

So, to use SUBSTRING correctly in PostgreSQL for the same exercise, it would look like this:

SELECT 
  name
FROM 
  students
WHERE 
  marks > 75
ORDER BY 
  SUBSTRING(name FROM LENGTH(name) - 2 FOR 3),
  id ASC;

Here, the starting index must be positive, and it counts from the beginning of the string.

DEV Community

HackerRank 'Higher than 75 marks' MySQL and PostgreSQL

Top comments (0)