We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
For the sake of discussion, I'll offer a differing opinion: having worked (in the US healthcare system in particular) for quite a number of years now, I've found that the most difficult part of solving a problem usually isn't the coding itself, but rather figuring out exactly what it is your client wants. This question is pretty clear compared to some of the client requests I've received in the past (albeit much simpler) - this isn't so much a statement about your interpretation of the question (which I agree with), but rather the challenges you'll encounter when non-technical individuals ask you to help them get technical answers, the problem itself isn't clear in the client's mind, there's a language gap between teams, etc. If every question posed is textbook-clear during training, coders will be ill-served when they finally reach 'real world' problems, which are as much about language and definitions as mathematics. Is the language of this question problematic? Sure. But I'd consider the process of thinking through what exactly the questioner wants a learning experience - one that will make the next unclear question clearer - and then move on to the next challenge.
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.
Andrew, I'm not sure what world you live in. In mine the requirements are typically vague and often absent all together.
In this world, I agree, your point make perfect sense, however. I wrote to the author using the "Suggested Edit" button to express my concern. Perhaps that's something you may consider.
I believe this problem statement is one of the more clearly written ones, however.
select city,length_city from (select a.*, rownum r from (select length(city) length_city,city from station
order by length_city, city) a)
where r in (1,(select count(*) from station));
SELECT * FROM(
( SELECT * FROM (
select CITY, LENGTH(CITY) K
FROM STATION
WHERE LENGTH(CITY) = ( SELECT MIN(LENGTH(CITY))
FROM STATION )
ORDER BY CITY ASC )
WHERE ROWNUM=1
) UNION
(SELECT * FROM (
select CITY, LENGTH(CITY) K
FROM STATION
WHERE LENGTH(CITY) = ( SELECT MAX(LENGTH(CITY))
FROM STATION )
ORDER BY CITY ASC )
WHERE ROWNUM=1
)
);
Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1;
Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
why write "city asc limit 1" rather than just "asc limit 1"? It seems like this made a difference in getting the right answer, but not sure what change that made
It works on MySQL , once I read your answer I know I thought too much,
I just need to write two independent queries then it will output the answer. I don't have to just write one query to output this.
Then, you have to change the ASC and DESC in order by statement,
SELECT CITY, LENGTH(CITY) FROM STATION WHERE ID = (SELECT ID FROM STATION ORDER BY LENGTH(CITY) ASC ,CITY DESC LIMIT 0,1);
* This will first sort the result by the length of the city, if the length of multiple rows are same then it will sort them in descending order of city name*\
select city ,length(city)
from station
where length(city)=
(select min(length(city)) from station order by length(city) asc);
select city,length(city) from station where length(city)=(select max(length(city))from station order by city asc);
the output is
Roy-3
Amo -3
Lee -3
Marine On Saint Croix 21
but i got the logic of what you have written a thumbs up...
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY LIMIT 1)
UNION
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 1)
I just added union to your code and put the select statements in separate brackets and it worked. Thanks!
(Select city, LENGTH(city) from station Order By Length(city), city asc limit 1) union
(Select city, LENGTH(city) from station Order By Length(city) desc, city desc limit 1);
[Second Query]Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
Here city should be asc as we need to sort city in ascending order of their occurance.
so final query
Select City,char_LENGTH(City) from Station Order By Length(City) desc, City asc limit 1;
(Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1)
union all
(Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1);
select CITY, length(CITY)from station order by length(CITY), city limit 1;
select CITY, length(CITY)from station order by length(CITY) desc, city limit 1;
`
a simple solution:
select small.a,b from
(select city a,length(city) b
from station
order by b, a)small where rownum=1
union
select big.a,b from
(select city a , length(city)b
from station
order by b desc ,a desc)big
where rownum=1;
select city,length(city) from (select city , length(city) from (select city from station order by length(city)) as a order by length(city),city limit 1) as b
union all
select city,length(city) from (select city , length(city) from (select city from station order by length(city)) as c order by length(city) desc,city limit 1) as d
wrong.
In the last query should be ordered by b desc, a ASC as we have to take the first alphabetically. But the table seems to have only one city with the largest string so you get luckily the expected results.
select CITY, length(CITY) from station order by length(CITY), city limit 1
select CITY, length(CITY) from station order by length(CITY) desc, city limit 1
i have written this expression in mysql ut its giving error:-
ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select CITY, length(CITY) from station order by length(CITY) desc, city limit 1' at line 2....what is the problem? pls rectify..pls help!!
You need semicolons after each query.
Your code should look like this:
select CITY, length(CITY) from station order by length(CITY), city limit 1;
select CITY, length(CITY) from station order by length(CITY) desc, city limit 1;
For Mysql
select city,char_length(city) from station order by city,char_length limit 1;
select city,char_length(city) from station
order by city desc,char_length desc limit 1;
SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY), CITY LIMIT 1;
SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY) DESC, CITY LIMIT 1;
I had a doubt,order by clause is applied only on len of city name ,does sorting of same length cities alphabetically happen implicitly . If it did happen by default ,what would be the query if we wanted it to be in desc order alphabetically
select city , length(city) from station order by length(city) desc , city desc limit 1;
select city , length(city) from station order by length(city) asc , city asc limit 1;
@adityaanand12345 normally it will be sorted in ascending order when you use ORDER but in this case we want to get the longest name so we use desc to sort it in descending order so we can get the first one.
order by clause used for sorting purpose like if you want to print something in ascending order or decending order , by default order by clause print ascending but if you want print name in descending order that time we use desc to sort
------------------------oracle----------------------------
select city,length(city) from (select city,rownum as rid from (select city,length(city) from station order by length(city),city))
where rid = 1 or rid = (select max(rownum) from station);
this answer is similar to the MySQL solutions but because LIMIT doesn't exist in Oracle we use rownum as rid to select within the order of length of city and city, we then say rid is 1 or the maximum rownum of station.
The a in the a.* is an alias. He used the 'a' bolded below to give the name to the select statement in front of that point so he could refrence it in the beginning. The a.* portion is refrencing all of the rows and columns in the 'a' query.
select city,length_city from
(select a., rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count() from station));
that cannot be the right query as you can have more than one city with the longest string and you are taking out the last one alphabetically ordered. Luckily the table seems to have only one city with the longest string, so you get the expected results. But that query does not follow the rules.
select t1.* from (select City, length(City) from station order by length(city) desc, city) t1 where rownum <= 1
union
select t2.* from (select City, length(City) from station order by length(city) , city) t2 where rownum <= 1;
For Oracle one, the query, though good, would not fulfil the logic demanded in the question. the question asked to get the minimum and maximum length of the cities along with the city name and if two cities have the same length , the output should be the first city in ascending order. By that logic, if in case there were two records having maximum city length, your query will fetch the last record, the the first city in ascending order withing maximum city length. Would like to know your opinion
For first row sort the table by city length in ascending and the than by city name also in ascending order limiting the output to 1
For second row sort the table by city length in descending and the than by city name in ascending order again limiting the output to 1.
select FIRST_VALUE(CITY) OVER (PARTITION BY LENGTH(CITY) ORDER BY CITY),LENGTH(CITY) from station where LENGTH(CITY) IN (select MIN(LENGTH(CITY)),MAX(LENGTH(CITY)) from station);
This seems to select the alphabetically last city for max length. The test does not catch this as it has only one city with max length. I modified this to:
select city, length(city) city_length from (select city, length(city) city_length from station order by city_length, city) where rownum = 1 union select city, length(city) city_length from (select city, length(city) city_length from station order by city_length desc, city) where rownum = 1;
what if longest name has multiple entries? for eg. rownum has values 1-500, but 496-500 are same. Task in this case is to pick 496th entry(alphabetical), but solution here picks 500th value not the 496th value.
This answer is good enough; the table has only one city name with a distinct length value of 21, however if there were two then this query will incorrectly select the last one of the two in alphabetical order.
select CITY,LENGTH(CITY) from STATION order by Length(CITY) asc, CITY limit 1;
select CITY,LENGTH(CITY) from STATION order by Length(CITY) desc, CITY limit 1;
this will work because we are not ordering the elements by their length we are doing it by the aplhabetical order so it does it.....and in the first query the result obtained will be having some set so we must pick out the top one...cool...
select top 1 city,len(city) as shortest from station order by len(city),city
select top 1 city,len(city) as longest from station order by len(city) desc, city
You dont have to "city desc" the second query because they want to display the city alphabetically.
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.
With all the 'real world' example you gave , you should understand that this problem you are giving is in the basics tab... so those who try this are actually learning the stuff, provided the fact that you don't even give summary tutorial for these problem .... let us know how can one learn (which is expected from these basic tutorial atleast).
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.
Path is structured for the fulfilment of the goals. The reforms of the dissertation writing serivce in all forms for the mid. Path is counted for the adaptable items for humans. The sort are feed for the approval of the terms. String is invited for the pictures of the field. work is issued for the Madoff the injunctions for the sorts of the field work for all items.
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.
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
For the sake of discussion, I'll offer a differing opinion: having worked (in the US healthcare system in particular) for quite a number of years now, I've found that the most difficult part of solving a problem usually isn't the coding itself, but rather figuring out exactly what it is your client wants. This question is pretty clear compared to some of the client requests I've received in the past (albeit much simpler) - this isn't so much a statement about your interpretation of the question (which I agree with), but rather the challenges you'll encounter when non-technical individuals ask you to help them get technical answers, the problem itself isn't clear in the client's mind, there's a language gap between teams, etc. If every question posed is textbook-clear during training, coders will be ill-served when they finally reach 'real world' problems, which are as much about language and definitions as mathematics. Is the language of this question problematic? Sure. But I'd consider the process of thinking through what exactly the questioner wants a learning experience - one that will make the next unclear question clearer - and then move on to the next challenge.
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.
Andrew, I'm not sure what world you live in. In mine the requirements are typically vague and often absent all together.
In this world, I agree, your point make perfect sense, however. I wrote to the author using the "Suggested Edit" button to express my concern. Perhaps that's something you may consider.
I believe this problem statement is one of the more clearly written ones, however.
select city,length_city from (select a.*, rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count(*) from station));
compiler is throwing error(wrong answer) for this query...
I DIDNOT GET ANY ERROR TRY ONE MORE TIME
THATS CODING WAS WRONG!!!!!
I am also getting error for using LIMIT statements.
for c#
select distinct top 1 city,LEN(city) from station order by len(city);select distinct top 1 city,LEN(city) from station order by len(city) desc;
This works, but I thought for a moment that in the case where a result set includes cities with the same length the query might fail if you don't do
order by citySay the result set looks like this:
Then wouldn't
select distinct top 1 city,LEN(city) from station order by len(city)return Bb instead of Aa.
So order by,
for min, .... order by len(city), city
for max, ... order by len(city) desc, city
true for mysql.. not oracle... would u know how to write this for oracle? Thanks
use length() instead of len() in ms sql server.
LEN() is used in sql server, too.
SELECT * FROM( ( SELECT * FROM ( select CITY, LENGTH(CITY) K FROM STATION WHERE LENGTH(CITY) = ( SELECT MIN(LENGTH(CITY)) FROM STATION ) ORDER BY CITY ASC ) WHERE ROWNUM=1 ) UNION (SELECT * FROM ( select CITY, LENGTH(CITY) K FROM STATION WHERE LENGTH(CITY) = ( SELECT MAX(LENGTH(CITY)) FROM STATION ) ORDER BY CITY ASC ) WHERE ROWNUM=1 ) );
WORKING...BUT EXPLAIN
/* FOR FIRST QUERY */ SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY ASC LIMIT 1;
/* FOR SECOND QUERY */ SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY DESC LIMIT 1;
for the second query, should be
ORDER BY LENGTH(CITY) DESC,CITY ASCinsteadgreat. . .It works fine. .Thnks
You don't need to put the first ORDER BY cause by default it's asc.
Try this:
Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1; Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
why write "city asc limit 1" rather than just "asc limit 1"? It seems like this made a difference in getting the right answer, but not sure what change that made
I think if you erase "city" from "city asc limit 1" then it will not maintain the alphabetical order.
no need for the asc and desc in the "city asc limit 1", that won't do a thing. cmiiw
Yes, that is correct. That's why it happens.
It works on MySQL , once I read your answer I know I thought too much, I just need to write two independent queries then it will output the answer. I don't have to just write one query to output this.
what if out of all the cities with the same minimum/maximum length , we wanted to select the city that came last in the alphabetical order ?
Then, you have to change the ASC and DESC in order by statement,
SELECT CITY, LENGTH(CITY) FROM STATION WHERE ID = (SELECT ID FROM STATION ORDER BY LENGTH(CITY) ASC ,CITY DESC LIMIT 0,1); * This will first sort the result by the length of the city, if the length of multiple rows are same then it will sort them in descending order of city name*\
but i tried in this way:
select city ,length(city) from station where length(city)= (select min(length(city)) from station order by length(city) asc);
Try This
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY LIMIT 1) UNION (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 1)
it works
Why LIMIT 1?
So that only one value gets printed.
but we also have to print cities of same lenghth! so will it work for that?
Since you just need to print one of the shortest or longest item.
this doesn't work in oracle. is there any keyword 'limit' in oracle cause i have serched and didn't found any??
Thats what im looking for bro. Beside "where rownum <= num" which wont work for this because of all the other stuff
In oracle, you can use ROWNUM to limit number of rows.
Thanks man!!
not working
I just added union to your code and put the select statements in separate brackets and it worked. Thanks!
(Select city, LENGTH(city) from station Order By Length(city), city asc limit 1) union (Select city, LENGTH(city) from station Order By Length(city) desc, city desc limit 1);
[Second Query]Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
Here city should be asc as we need to sort city in ascending order of their occurance. so final query Select City,char_LENGTH(City) from Station Order By Length(City) desc, City asc limit 1;
This is more clean. Thanks!!
(Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1) union all (Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1);
Same
try in oracle. if you try in SQL then its not work. It will work only in oracle plateform;
that query is running perfectly.
-- For MySql
select CITY, length(CITY)from station order by length(CITY), city limit 1; select CITY, length(CITY)from station order by length(CITY) desc, city limit 1; `
Oh! You're right. I didn't change the SQL system. Thanks!
can u plz explain this thing?
a simple solution: select small.a,b from (select city a,length(city) b from station order by b, a)small where rownum=1 union select big.a,b from (select city a , length(city)b from station order by b desc ,a desc)big where rownum=1;
Can you please explain this?
select city,length(city) from (select city , length(city) from (select city from station order by length(city)) as a order by length(city),city limit 1) as b union all select city,length(city) from (select city , length(city) from (select city from station order by length(city)) as c order by length(city) desc,city limit 1) as d
should work for mysql
There is nothing to explain in oracale to select selected number of raw we used ROWNUM and in MUSQL we use LIMIT
IN BIGGEST A SHOULD BE SORTED ASC BECAUSE WE NEED THE FIRST ENTRY NOT THE LAST FOR SAME LENBGTH CASE
wrong. In the last query should be ordered by b desc, a ASC as we have to take the first alphabetically. But the table seems to have only one city with the largest string so you get luckily the expected results.
select CITY, length(CITY) from station order by length(CITY), city limit 1 select CITY, length(CITY) from station order by length(CITY) desc, city limit 1
i have written this expression in mysql ut its giving error:- ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select CITY, length(CITY) from station order by length(CITY) desc, city limit 1' at line 2....what is the problem? pls rectify..pls help!!
You need semicolons after each query. Your code should look like this:
select CITY, length(CITY) from station order by length(CITY), city limit 1; select CITY, length(CITY) from station order by length(CITY) desc, city limit 1;
select city, length(city) from station order by length(city), city limit 1; select city, length(city) from station order by length(city) desc limit 1;
THIS CODE IS NOT WORKING FOR ME
For Mysql select city,char_length(city) from station order by city,char_length limit 1; select city,char_length(city) from station order by city desc,char_length desc limit 1;
It was easier than I thought, good job
WHY U HAVE WRITTEN CITY LIMIT 1 AND DESC CITY LIMIT 1 IN DOWN LNE CAN U EXPLAIN
wrong doesnt work for mySql
SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY), CITY LIMIT 1; SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY) DESC, CITY LIMIT 1;
what does the LIMIT keyword do?
The LIMIT keyword limit the number of record as you expect
You can learn more here "https://www.w3schools.com/sql/default.asp"
Limit will help you to select only particular number of entries e.g. 1 in this case
suppose there are multiple cities with min length ( as it is in this case, 3 cities) , your 'limit' displays the number of cities as per set by you.
I had a doubt,order by clause is applied only on len of city name ,does sorting of same length cities alphabetically happen implicitly . If it did happen by default ,what would be the query if we wanted it to be in desc order alphabetically
I don't think it would work if the there are multiple cities with minmum length for eg : Ame Aao Azi
You might have to sort it first.
select city , length(city) from station order by length(city) desc , city desc limit 1; select city , length(city) from station order by length(city) asc , city asc limit 1;
in the first one , -> city asc limit 1; should be there
I am also confused about that. we should limit answer to 1 and how can we sort it by alphabetic
Its by default.
why we can't just use limit why we are using city limit 1???
same question here also if we dont use city limit it wont give answer
What is the use of limit?
add ; after the first query
just use UNION between the two queries to combine the result
Oracle code is not working
Can you just explain the desc part. I understood the whole part but didn't get this particular part
@adityaanand12345 normally it will be sorted in ascending order when you use ORDER but in this case we want to get the longest name so we use desc to sort it in descending order so we can get the first one.
The desc part just orders the query in descending order.
by default order by sorts in ascending order, U need to specify desc so that it does it in descending order ie A-Z!!
A-Z is ascending, and Z-A descending, I think.
order by clause used for sorting purpose like if you want to print something in ascending order or decending order , by default order by clause print ascending but if you want print name in descending order that time we use desc to sort
------------------------oracle---------------------------- select city,length(city) from (select city,rownum as rid from (select city,length(city) from station order by length(city),city)) where rid = 1 or rid = (select max(rownum) from station);
could you please explain this query ?
Great solution
this answer is similar to the MySQL solutions but because LIMIT doesn't exist in Oracle we use rownum as rid to select within the order of length of city and city, we then say rid is 1 or the maximum rownum of station.
can u plz explain this answer....
Can U tell me how work a.* ?
The a in the a.* is an alias. He used the 'a' bolded below to give the name to the select statement in front of that point so he could refrence it in the beginning. The a.* portion is refrencing all of the rows and columns in the 'a' query.
select city,length_city from (select a., rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count() from station));
plzz explain Y u hav use city before limit 1 in D query???
so that you can limit the value of the city column to 1 as you only need one output in each case
k..
what is the use of limit?
explanation for oracle pls....!!
thanx
YOU query is absolutely right...
Thk u sir...
In Oracle, what does that select a.* do?? what is a here?
please explain the code as a beginner i didnt get it..
can u explain this one
Why do we have to add city in front of limit?
I didn't understand the sql one. specially the desc?
its for descending sorting.
sir,what is use of city limit ? i am beginner....
Select * FROM (select CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY), CITY LIMIT 1) AS T;
Select * FROM (select CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY) DESC, CITY LIMIT 1) AS T;
"order by... limit x" means giving the first x numbers of line with this order
it is not working
that cannot be the right query as you can have more than one city with the longest string and you are taking out the last one alphabetically ordered. Luckily the table seems to have only one city with the longest string, so you get the expected results. But that query does not follow the rules.
This will not work if we have more than one cities of max length.
its correct.
-- another way for oracle:
select t1.* from (select City, length(City) from station order by length(city) desc, city) t1 where rownum <= 1 union select t2.* from (select City, length(City) from station order by length(city) , city) t2 where rownum <= 1;
thankyou, :)
can u please explain , what does "select a.*, " do
WHY U HAVE WRITTEN CITY LIMIT 1
For Oracle one, the query, though good, would not fulfil the logic demanded in the question. the question asked to get the minimum and maximum length of the cities along with the city name and if two cities have the same length , the output should be the first city in ascending order. By that logic, if in case there were two records having maximum city length, your query will fetch the last record, the the first city in ascending order withing maximum city length. Would like to know your opinion
The MySql solution is quite easy while the oracle one is confusing me. Can you please explain how the oracle solution is working.
THANKS
ya,i too agree..the complier is showing me as wring answer for this query
coding is right, try it in oracle
select city, length(city) from station order by length(city) ASC limit 1; select city, length(city) from station order by length(city) DESC limit 1;
showing error
There is a simpler way.
Code wont work for following city names data: ABC PQR QXY PQRS DEFG
you code will give: ABC 3 PQRS 4
But we need ABC 3 DEFG 4
For first row sort the table by city length in ascending and the than by city name also in ascending order limiting the output to 1 For second row sort the table by city length in descending and the than by city name in ascending order again limiting the output to 1.
Can't we do this in one single query rather than 2?
Here you go
select FIRST_VALUE(CITY) OVER (PARTITION BY LENGTH(CITY) ORDER BY CITY),LENGTH(CITY) from station where LENGTH(CITY) IN (select MIN(LENGTH(CITY)),MAX(LENGTH(CITY)) from station);
SIR, THANK YOU.
this query is for oracle
This seems to select the alphabetically last city for max length. The test does not catch this as it has only one city with max length. I modified this to:
select city, length(city) city_length from (select city, length(city) city_length from station order by city_length, city) where rownum = 1 union select city, length(city) city_length from (select city, length(city) city_length from station order by city_length desc, city) where rownum = 1;
plz explain query and also expalin a.*
It works fine. But could you please explain your query
what if longest name has multiple entries? for eg. rownum has values 1-500, but 496-500 are same. Task in this case is to pick 496th entry(alphabetical), but solution here picks 500th value not the 496th value.
I didnt fully understand the code. What is select a.*, rownum r from() part doing?
This answer is good enough; the table has only one city name with a distinct length value of 21, however if there were two then this query will incorrectly select the last one of the two in alphabetical order.
what a.* do ? and what is a here ?
It is perfectly worked
COMPILER THROUGH AN ERROR
select CITY,LENGTH(CITY) from STATION order by Length(CITY) asc, CITY limit 1; select CITY,LENGTH(CITY) from STATION order by Length(CITY) desc, CITY limit 1;
this will work
very nice;
working without errors...thanks
This code will get compiled successfully in oracle.
for SQL Server:
this will work because we are not ordering the elements by their length we are doing it by the aplhabetical order so it does it.....and in the first query the result obtained will be having some set so we must pick out the top one...cool...
Thank you.....worked for me!!
select top 1 city,len(city) as shortest from station order by len(city),city select top 1 city,len(city) as longest from station order by len(city) desc, city
You dont have to "city desc" the second query because they want to display the city alphabetically.
He ordered DESC in the second query to get the maximum length. By ordering it ASC in the forst query, he got the city with the minimum length.
Andrew, were you finally able to solve it?
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.
Very well put.
The same reply over and over again, minus point.
With all the 'real world' example you gave , you should understand that this problem you are giving is in the basics tab... so those who try this are actually learning the stuff, provided the fact that you don't even give summary tutorial for these problem .... let us know how can one learn (which is expected from these basic tutorial atleast).
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.
Path is structured for the fulfilment of the goals. The reforms of the dissertation writing serivce in all forms for the mid. Path is counted for the adaptable items for humans. The sort are feed for the approval of the terms. String is invited for the pictures of the field. work is issued for the Madoff the injunctions for the sorts of the field work for all items.
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
Nice approach to look at problem!
That was great help Thanks!. It helped me to read the problem more carefully and i was able to solve it in no time!