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