CODEWITHSUNDEEP
sqloracle-database
borngeek
borngeek

Reputation: 77

Unable to sort results in table based on length of string

This is in reference to a hackerRank problem. I am facing a weird problem. I am unable to sort an output of an SQL query:

SELECT CITY, LENGTH(CITY) AS LEN FROM STATION 
WHERE
LENGTH(CITY) IN (
(SELECT MIN(LENGTH(CITY)) FROM STATION),
(SELECT MAX(LENGTH(CITY)) FROM STATION))
ORDER BY TO_NUMBER(LEN) ASC;

The desired output should be:

Amo 3 
Lee 3  
Roy 3
Marine On Saint Croix 21

Whereas I am getting the following as output:

Amo 3 
Lee 3 
Marine On Saint Croix 21 
Roy 3

Upvotes: 0

Views: 113

Answers (4)

MtwStark
MtwStark

Reputation: 4058

The problem is HackerRank environment, not your query.

if you try to run it on Oracle Live SQL it works just fine.

changing ORDER BY TO_NUMBER(LEN) to ORDER BY LEN will be an optimization

Upvotes: 0

MT0
MT0

Reputation: 168001

The question is:

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

And in the submission box there is a comment:

Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.

So, you have several errors:

  1. Your desired output is not actually the desired output from the question (you are returning too many rows as you only want a single shortest city name and a single longest city name which are alphabetically first if there are ties on length).
  2. You need to make sure the code is all on a single line (not multiple lines as you have posted here).
  3. You should probably be looking at using two queries each with WHERE ROWNUM = 1 to get the shortest and longest names respectively and then using UNION (or UNION ALL) to merge them or a single query using the ROW_NUMBER() analytic function.

Upvotes: 0

Leo
Leo

Reputation: 549

You are add alias to the column and sort it in the same level of the statement. You need to rewrite it like this I guess:

SELECT CITY, LENGTH(CITY) AS LEN FROM STATION 
WHERE
LENGTH(CITY) IN (
(SELECT MIN(LENGTH(CITY)) FROM STATION),
(SELECT MAX(LENGTH(CITY)) FROM STATION))
ORDER BY LENGTH(CITY) ASC;

or you also can wrap statement like this:

SELECT * 
  FROM (SELECT CITY, LENGTH(CITY) AS LEN FROM STATION 
         WHERE LENGTH(CITY) IN (
                 (SELECT MIN(LENGTH(CITY)) FROM STATION),
                 (SELECT MAX(LENGTH(CITY)) FROM STATION))) t
ORDER BY t.LEN ASC;

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

The code is not clean, but logically it is fine.
I can think on 2 options.

1) Not the whole query is being executed. The order by clause is left out.
2) An additional sort is being done in your execution environment.

Care to share how do you execute this code?

Upvotes: 1

Related Questions