Yeah, I totally agree, but I'm not sure that's something a site like this can gauge well, as there's no back-and-forth with whomever asked the question. In situations involving ambiguous requirements, a direct clarifying question will work wonders, but I haven't seen a response or clarification despite a direct question. In the real world, if you give me unclear requirements and don't respond when I ask for clarification, you get the result of my best guess as to what you meant. Here, there are ambigious requirements and a very specific test case you must pass.

In a real-world scenario, you meet and/or communicate with the client about their spec to confirm and clarify that you understand what they are asking for. After these issues have been addressed--in a real-world scenario--you often even enter into a clear contract with said client about precisely what will be delivered.

Yes, this problem was badly worded and it wasn't hard to figure out what they wanted based on the given examples. Yes, developing the ability to intuit what your client needs out of the mess of what they think they need is an important life skill; however, this is not real life. This is a website that markets themselves as a tool for teaching, training, and exercising code skills. The exercises on this website are a professional business product, and the users act as the client. There is no mode of instant contact with the problem's poster. It is more than reasonable for users to expect concise problem descriptions and accurate solution testing, just as you would expect from exercises in a textbook.

Agree except one can ask clarifying questions from a real life client. Trying "SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY);" is simplistic but under certain assumptions could also be correct. In other words, logically the question can be proved ambiguous and there is no recourse for clarifying questions.

I appreciate the real world input and definitely agree. I feel as I practice I should become more versed in both sides, the business and technical and this site is providing that free.

Well said man. I cant agree more.

true statement and well said

I expected from the way it was worded that it was expecting one recordset, so UNION or similar. Should rephrase to suggest two recordsets.

Oracle provides the FETCH FIRST mechanism which allows you to select n rows with order by. This is much better then SELECT * around your whole statement.

For example:

SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY FETCH FIRST 1 ROWS ONLY;

Note: The Orderby is executed before we fetch the first row.

simplest answer : (select city,(length(city)) from station order by length(city) desc ,city limit 1)

what is the need of 'city' after lenght(city) in the statement? similary after length(city) desc, city.. why ? SELECT * FROM(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY) WHERE ROWNUM = 1; SELECT * FROM(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY) WHERE ROWNUM = 1;

select CITY,length(city) from station WHERE ROWNUM <= 1 order by length(city),CITY desc; select CITY,length(city) from station WHERE ROWNUM <= 1 order by length(city) ASC;

why this code is incorrect?

SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) limit 1; SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) desc limit 1;

this code was a modification of onthekineguy

If you have a rownum (i.e. Oracle) you can't use it in one query with order by, the rownum is assigned before order by is applied. You can wrap it in another query and use rownum there, or use row_number, or switch db.

The problem is that rownum limits your rows before order by is executed. Use FETCH FIRST mechanism i described in comment above.

If you want use rownum, you should rewrite code like this

SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)) WHERE ROWNUM <= 1;

Can u plz explain it

Thank you ... It worked for me.

You can use length

in oracle you can use WHERE ROWNUM = 1 instead

Try this:

select city, length(city) from station order by length(city) asc, city asc limit 1; select city, length(city) from station order by length(city) desc, city asc limit 1;

no, instead you can use rownum in oracle

TOP in Orcale will be in the WHERE clause AS "WHERE ROWNUM = N". This one is a big catch because each SGBD works differently with the TOP concept. For better details, see http://www.w3schools.com/sql/sql_top.asp All the best!

YES IN ORACLE TOP IS PRESENT..

/Better Solution SQL SERVER:/ select min(city),len(min(city)) from STATION union select min(city),len(min(city)) from STATION where len(city) =(select len(max(city)) from STATION)

I went with the CTE, but it doesn't work when I run it here. On SQL Server it returns the answer properly: with n as (select city, len(city) citylen from [SalesLeads_New].[dbo].[ba_car_cooper] group by city )select top 1 city,citylen from n order by citylen desc; with n as (select city, len(city) citylen from [SalesLeads_New].[dbo].[ba_car_cooper] group by city )select top 1 city,citylen from n order by citylen asc

is there a way to do in mysql?

It is outside as you want to return just rownum = 1 from the inner query. The inner query is getting all the city and lengths, then orders it, the outside query is selecting all and then grabbing rownum = 1 from the inner query. If you do rownum = 1 in the inner query it will return the first row before it does the order by clause so you will not get the desired results.

https://www.periscopedata.com/blog/sql-query-order-of-operations for more details

Because if there are multiple results with the same length you will have to get the result that comes first alphabetically.

The ASC in the end of it can be ignored but I like to put them in for clarification :)

SELECT TOP 1 CITY, LEN(CITY) AS LENGTH
FROM STATION 
ORDER BY LEN(CITY) ASC, CITY ASC;

SELECT TOP 1 CITY, LEN(CITY) AS LENGTH
FROM STATION 
ORDER BY LEN(CITY) DESC, CITY ASC;

If someone still doesn't understand,

basically with the ORDER BY clause you can sort by multiple things, not just one. So in this example,

when the following code is executed:

ORDER BY LEN(CITY), CITY

you are first ordering by LEN(CITY) meaning that the query will order the results by the length of the city name.

So all of the city names with 3 letters, then 4, then 5, and so on....

But, this is all it does, and it will not order them any other way because you did not explicity say to do so. So now the length of the city name is sorted in ascending order, but the results are not alphabetical. This is where the ,CITY part comes in.

in SQL SERVER, the following code works

SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY) ASC, CITY; SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY) DESC, CITY;