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.
This question needs to be rewritten for clarity. "the possible minimum value" is completely unclear and doesn't give any idea as to the scope of the question. The shortest city name in the table is my best guess, but it shouldn't be a guessing game.
In fact, this question is so unclear and contrived I really think it should be removed.
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.
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)
[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, 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);
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.
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.
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.
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;
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.
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))
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;
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;
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.
select top 1 CITY,min(len(CITY)) len from STATION group by CITY order by len asc;
select top 1 CITY,max(len(CITY)) len from STATION group by CITY order by len desc;
Just getting in to SQL Server and was poking around for a clear example of how to implement variables - thanks for this! Seeing this implementation helped me connect some dots.
This is the right query for oracle
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 city , length(city) from station order by length(city) desc, city desc) where rownum = 1
union
select * from (select city , length(city) from station order by length(city) asc, city asc) where rownum = 1;
This question is pretty clear it wants you to query for two cities, one with the longest name and one with the shortest name, and if there is a tie for either then use the city name that comes first alphabetically.
If you have to resort to guessing then there is an issue with the query methodology.
This query can be solved dynamically using MIN and Max, or even the ORDER BY clause with LIMIT.
select * From
(
select CITY, length(CITY)
from Station
order by length(CITY) desc, city
)
Where rownum=1;
select * From
(
select CITY, length(CITY)
from Station
order by length(CITY) asc, city
)
Where rownum=1;
--- Oracle you need to use rownum=1 to limit to one observation. You need to first sort everything within the subquery and then use rownum to limit it to 1 value.
select city, len(city) FROM STATION where city in (Select top 1 city FROM STATION order by len(city), city ) or city in (Select top 1 city FROM STATION order by len(city) desc ) ;
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
This question needs to be rewritten for clarity. "the possible minimum value" is completely unclear and doesn't give any idea as to the scope of the question. The shortest city name in the table is my best guess, but it shouldn't be a guessing game.
In fact, this question is so unclear and contrived I really think it should be removed.
Agreed, it makes very little sense, given the context.
[deleted]
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
great. . .It works fine. .Thnks
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 ?
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
Thanks man!!
not working
[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;
Same
try in oracle. if you try in SQL then its not work. It will work only in oracle plateform;
-- 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
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?
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
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.
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.
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!
The question is rewritten; please comment on if you think anything additional needs to be changed or updated.
I expected from the way it was worded that it was expecting one recordset, so UNION or similar. Should rephrase to suggest two recordsets.
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;
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:
Note: The Orderby is executed before we fetch the first row.
Yes, this came with Oracle Database 12c (https://docs.oracle.com/database/121/LNPLS/release_changes.htm#LNPLS113).
I get an error when trying this:
this is not a right query or syntex. please do read about syntex of "ORDER BY" and also on "HAVING" clause.
the syntax for
ORDER BYis correct. If I run the exact same query without theFETCHclause then the query works just fine.Going off these docs, my query with
FETCHshould work just fine. https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1Try this in "oracle" envoirment:
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))
Yes I'm using Oracle DB, as my original post stated.
Thank you for your answer, but I am wondering why Oracle's
FETCHclause is not working here...Can you explain this query?
if there are more cities with length 21 this doesnt work
Semicolon is missing
can you explain this code
NOT WORKING
simplest answer : (select city,(length(city)) from station order by length(city) desc ,city limit 1)
Throwing as wrong answer
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;
AFTER ORDERING FOR LENGTH IN ASC ORDER, YOU ALSO NEED TO ORDER THE CITIES ALPHABETICALLY.
If you dont mention any order, by default is Ascending order. So that query is right.
can you explain how it is working without the min and max ?
you are ordering it and limiting it to a single row hence your aim is fulfilled.
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?
EVEN THIS QUERY IS GIVING WRONG ANSWER....SOMETHING IS WRONG
SELECT CITY, LENGTH(CITY) FROM STATION WHERE ROWNUM <= 1 ORDER BY LENGTH(CITY);
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
thank you it works for me
could you tell me how "MIN(CITY)" works? How could a STRING use MIN function?
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
SET @V1 := (SELECT MIN(LENGTH(CITY)) FROM STATION); SET @V2 := (SELECT CITY FROM STATION WHERE LENGTH(CITY) = @V1 ORDER BY CITY LIMIT 1);
SET @V3 := (SELECT MAX(LENGTH(CITY)) FROM STATION); SET @V4 := (SELECT CITY FROM STATION WHERE LENGTH(CITY) = @V3 ORDER BY CITY LIMIT 1);
SELECT @V2,@V1; SELECT @V4,@V3;
This is the best answer for MYSQL, thanks
I customised it for MS SQL Server as below:
DECLARE @V1 VARCHAR(MAX)
DECLARE @V2 VARCHAR(MAX)
DECLARE @V3 VARCHAR(MAX)
DECLARE @V4 VARCHAR(MAX)
SET @V1 = (SELECT MIN(LEN(CITY)) FROM STATION);
SET @V2 = (SELECT TOP 1 CITY FROM STATION WHERE LEN(CITY) = @V1 ORDER BY CITY );
SET @V3 = (SELECT MAX(LEN(CITY)) FROM STATION);
SET @V4 = (SELECT TOP 1 CITY FROM STATION WHERE LEN(CITY) = @V3 ORDER BY CITY );
SELECT @V2,@V1; SELECT @V4,@V3;
select top 1 CITY,min(len(CITY)) len from STATION group by CITY order by len asc; select top 1 CITY,max(len(CITY)) len from STATION group by CITY order by len desc;
Thank you
Just getting in to SQL Server and was poking around for a clear example of how to implement variables - thanks for this! Seeing this implementation helped me connect some dots.
Without use of variables (MySQL):
Hi, I'm wondering - why does WHERE LENGTH(CITY) = MAX(LENGTH(CITY)) not work? I get a "Invalid use of group function" error.
Double check that you're not using Oracle, but that you're using MySQL
That's because you need to use 'having' when you use a group function like max or length
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY LIMIT 1;
what the is use of writing city before limit???
It works, but also add city to order by list
Simple and elegant. Nice.
I am also agree with you.
SELECT TOP(1) CITY, LEN(CITY) AS CITY_LEN FROM STATION GROUP BY CITY ORDER BY LEN(CITY) DESC;
SELECT TOP(1) CITY, LEN(CITY) AS CITY_LEN FROM STATION GROUP BY CITY ORDER BY LEN(CITY) ASC;
This is the right query for oracle 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));
Can u plz explain it
select * from (select city , length(city) from station order by length(city) desc, city desc) where rownum = 1 union select * from (select city , length(city) from station order by length(city) asc, city asc) where rownum = 1;
Thank you ... It worked for me.
I think the question is straight forward. They have also given the example. Check the example and you will be able to understand.
HOW TO COUNT CHARACTERS IN A STRING??
You can use length
SHUT YOUR MOUTH BRENDA
This question is pretty clear it wants you to query for two cities, one with the longest name and one with the shortest name, and if there is a tie for either then use the city name that comes first alphabetically.
If you have to resort to guessing then there is an issue with the query methodology.
This query can be solved dynamically using MIN and Max, or even the ORDER BY clause with LIMIT.
select * From ( select CITY, length(CITY) from Station order by length(CITY) desc, city ) Where rownum=1;
select * From ( select CITY, length(CITY) from Station order by length(CITY) asc, city ) Where rownum=1;
--- Oracle you need to use rownum=1 to limit to one observation. You need to first sort everything within the subquery and then use rownum to limit it to 1 value.
yup i agree with you
select city, len(city) FROM STATION where city in (Select top 1 city FROM STATION order by len(city), city ) or city in (Select top 1 city FROM STATION order by len(city) desc ) ;
done
at the end just add order by city ;
Indians..