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.
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)
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
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
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;
great. . .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 ?
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
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;
-- 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